| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Complicated Merge
I have several Access tables (that have one to many relationships) that I've
combined into one query, and I need to create a fairly complex merge in Word with it. The table's data is like this with the first row being the column heads: Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Water Water Rights 1234 Main St. 676767 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 The resultant merge document should look something like this: Issue: Air Programs: Air Permits Air Quality Facility: 1234 Main St. Permit Numbers: 123456 ------------------------------ Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ------------------------------ Issue: Waste Programs: Waste Permits Facility: 1234 Main St. Permit Numbers: 898989 Facility: 8888 1st St. Permit Numbers: 101010 I have seen how to do a compound merge where there are only two columns in the query, but I can't figure out how to do it with this many columns. Can anyone help with this? |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Complicated Merge
It seems to me that there are potentially at least 2 problems he
a. Word mailmerge is pretty bad at "one-to-many" stuff to begin with and b. it isn't completely clear from your example exactly what possibilities can occur in your data. I've tried to envisage what you might have been starting from but there seem to be several possibilities. and I suppose in the end it boils down to c. how is the output going to be used? For example, could you have one less record, e.g. Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 In that case, would you want Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ? since there would be no Pemit Number 676767 for 1234 Main St pertaining to Water Rights. If not, what would you want? At the moment it looks as if your primary sort sequence is by Issue, but beyond that, what? As for how to do it in Word, a. can you do it in Access reporting? b. if not, I'd suggest whatever the output needs to look like, it's probably going to need two steps: - sequence the information exactly as it needs to appear in your report (that's the hard part, IMO, because it seems to me to be hard to do using a query language such as SQL) - work out how to produce the report from that sequence. Peter Jamieson "Joshann" wrote in message ... I have several Access tables (that have one to many relationships) that I've combined into one query, and I need to create a fairly complex merge in Word with it. The table's data is like this with the first row being the column heads: Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Water Water Rights 1234 Main St. 676767 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 The resultant merge document should look something like this: Issue: Air Programs: Air Permits Air Quality Facility: 1234 Main St. Permit Numbers: 123456 ------------------------------ Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ------------------------------ Issue: Waste Programs: Waste Permits Facility: 1234 Main St. Permit Numbers: 898989 Facility: 8888 1st St. Permit Numbers: 101010 I have seen how to do a compound merge where there are only two columns in the query, but I can't figure out how to do it with this many columns. Can anyone help with this? |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Complicated Merge
Thanks for your reply. I'm trying to create a report in Word that looks like
the example I gave below. Maybe it would help if showed you how the tables are actually set up in Access. Table Issues has a one to many relationship with table Programs. Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. I created a single query that combines all the data so that I could permform a mail merge in Word, and the data in the query looks like the example I provided below. Yes, I can create a report in Access that will look like the example I gave below. The problem is that I can't use an Access report because my users have to be able to add all kinds of text to the report. In other words, the report needs to be totally editable. Any help you can provide would be greatly appreciated because I am totally stuck. "Peter Jamieson" wrote: It seems to me that there are potentially at least 2 problems he a. Word mailmerge is pretty bad at "one-to-many" stuff to begin with and b. it isn't completely clear from your example exactly what possibilities can occur in your data. I've tried to envisage what you might have been starting from but there seem to be several possibilities. and I suppose in the end it boils down to c. how is the output going to be used? For example, could you have one less record, e.g. Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 In that case, would you want Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ? since there would be no Pemit Number 676767 for 1234 Main St pertaining to Water Rights. If not, what would you want? At the moment it looks as if your primary sort sequence is by Issue, but beyond that, what? As for how to do it in Word, a. can you do it in Access reporting? b. if not, I'd suggest whatever the output needs to look like, it's probably going to need two steps: - sequence the information exactly as it needs to appear in your report (that's the hard part, IMO, because it seems to me to be hard to do using a query language such as SQL) - work out how to produce the report from that sequence. Peter Jamieson "Joshann" wrote in message ... I have several Access tables (that have one to many relationships) that I've combined into one query, and I need to create a fairly complex merge in Word with it. The table's data is like this with the first row being the column heads: Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Water Water Rights 1234 Main St. 676767 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 The resultant merge document should look something like this: Issue: Air Programs: Air Permits Air Quality Facility: 1234 Main St. Permit Numbers: 123456 ------------------------------ Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ------------------------------ Issue: Waste Programs: Waste Permits Facility: 1234 Main St. Permit Numbers: 898989 Facility: 8888 1st St. Permit Numbers: 101010 I have seen how to do a compound merge where there are only two columns in the query, but I can't figure out how to do it with this many columns. Can anyone help with this? |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Complicated Merge
Table Issues has a one to many relationship with table Programs.
Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. OK, so how in that case are you able to write a query that returns 1 row as follows: Waste Waste Permits 1234 Main St. 898989 ? Is there a one-many between Programs and PermitNumbers? And I don't necessarily mean a relationship defined in Access, but how are you doing the join? Maybe it would be easier to show us the query SQL you're using :-) Other than that, I'll have to give this more thought... Peter Jamieson "Joshann" wrote in message ... Thanks for your reply. I'm trying to create a report in Word that looks like the example I gave below. Maybe it would help if showed you how the tables are actually set up in Access. Table Issues has a one to many relationship with table Programs. Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. I created a single query that combines all the data so that I could permform a mail merge in Word, and the data in the query looks like the example I provided below. Yes, I can create a report in Access that will look like the example I gave below. The problem is that I can't use an Access report because my users have to be able to add all kinds of text to the report. In other words, the report needs to be totally editable. Any help you can provide would be greatly appreciated because I am totally stuck. "Peter Jamieson" wrote: It seems to me that there are potentially at least 2 problems he a. Word mailmerge is pretty bad at "one-to-many" stuff to begin with and b. it isn't completely clear from your example exactly what possibilities can occur in your data. I've tried to envisage what you might have been starting from but there seem to be several possibilities. and I suppose in the end it boils down to c. how is the output going to be used? For example, could you have one less record, e.g. Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 In that case, would you want Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ? since there would be no Pemit Number 676767 for 1234 Main St pertaining to Water Rights. If not, what would you want? At the moment it looks as if your primary sort sequence is by Issue, but beyond that, what? As for how to do it in Word, a. can you do it in Access reporting? b. if not, I'd suggest whatever the output needs to look like, it's probably going to need two steps: - sequence the information exactly as it needs to appear in your report (that's the hard part, IMO, because it seems to me to be hard to do using a query language such as SQL) - work out how to produce the report from that sequence. Peter Jamieson "Joshann" wrote in message ... I have several Access tables (that have one to many relationships) that I've combined into one query, and I need to create a fairly complex merge in Word with it. The table's data is like this with the first row being the column heads: Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Water Water Rights 1234 Main St. 676767 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 The resultant merge document should look something like this: Issue: Air Programs: Air Permits Air Quality Facility: 1234 Main St. Permit Numbers: 123456 ------------------------------ Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ------------------------------ Issue: Waste Programs: Waste Permits Facility: 1234 Main St. Permit Numbers: 898989 Facility: 8888 1st St. Permit Numbers: 101010 I have seen how to do a compound merge where there are only two columns in the query, but I can't figure out how to do it with this many columns. Can anyone help with this? |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Complicated Merge
Thanks for your help, but I've decided to create the document using VBA. I'm
pretty sure I can do it. Again, thanks for your time. "Peter Jamieson" wrote: Table Issues has a one to many relationship with table Programs. Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. OK, so how in that case are you able to write a query that returns 1 row as follows: Waste Waste Permits 1234 Main St. 898989 ? Is there a one-many between Programs and PermitNumbers? And I don't necessarily mean a relationship defined in Access, but how are you doing the join? Maybe it would be easier to show us the query SQL you're using :-) Other than that, I'll have to give this more thought... Peter Jamieson "Joshann" wrote in message ... Thanks for your reply. I'm trying to create a report in Word that looks like the example I gave below. Maybe it would help if showed you how the tables are actually set up in Access. Table Issues has a one to many relationship with table Programs. Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. I created a single query that combines all the data so that I could permform a mail merge in Word, and the data in the query looks like the example I provided below. Yes, I can create a report in Access that will look like the example I gave below. The problem is that I can't use an Access report because my users have to be able to add all kinds of text to the report. In other words, the report needs to be totally editable. Any help you can provide would be greatly appreciated because I am totally stuck. "Peter Jamieson" wrote: It seems to me that there are potentially at least 2 problems he a. Word mailmerge is pretty bad at "one-to-many" stuff to begin with and b. it isn't completely clear from your example exactly what possibilities can occur in your data. I've tried to envisage what you might have been starting from but there seem to be several possibilities. and I suppose in the end it boils down to c. how is the output going to be used? For example, could you have one less record, e.g. Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 In that case, would you want Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ? since there would be no Pemit Number 676767 for 1234 Main St pertaining to Water Rights. If not, what would you want? At the moment it looks as if your primary sort sequence is by Issue, but beyond that, what? As for how to do it in Word, a. can you do it in Access reporting? b. if not, I'd suggest whatever the output needs to look like, it's probably going to need two steps: - sequence the information exactly as it needs to appear in your report (that's the hard part, IMO, because it seems to me to be hard to do using a query language such as SQL) - work out how to produce the report from that sequence. Peter Jamieson "Joshann" wrote in message ... I have several Access tables (that have one to many relationships) that I've combined into one query, and I need to create a fairly complex merge in Word with it. The table's data is like this with the first row being the column heads: Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Water Water Rights 1234 Main St. 676767 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 The resultant merge document should look something like this: Issue: Air Programs: Air Permits Air Quality Facility: 1234 Main St. Permit Numbers: 123456 ------------------------------ Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ------------------------------ Issue: Waste Programs: Waste Permits Facility: 1234 Main St. Permit Numbers: 898989 Facility: 8888 1st St. Permit Numbers: 101010 I have seen how to do a compound merge where there are only two columns in the query, but I can't figure out how to do it with this many columns. Can anyone help with this? |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Complicated Merge
Or just use a report in Access.
-- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Joshann" wrote in message ... Thanks for your help, but I've decided to create the document using VBA. I'm pretty sure I can do it. Again, thanks for your time. "Peter Jamieson" wrote: Table Issues has a one to many relationship with table Programs. Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. OK, so how in that case are you able to write a query that returns 1 row as follows: Waste Waste Permits 1234 Main St. 898989 ? Is there a one-many between Programs and PermitNumbers? And I don't necessarily mean a relationship defined in Access, but how are you doing the join? Maybe it would be easier to show us the query SQL you're using :-) Other than that, I'll have to give this more thought... Peter Jamieson "Joshann" wrote in message ... Thanks for your reply. I'm trying to create a report in Word that looks like the example I gave below. Maybe it would help if showed you how the tables are actually set up in Access. Table Issues has a one to many relationship with table Programs. Table Issues has a one to many relationship with table Facilities. Table Programs and table Facilities are not related. Table Facilities has a one to many relationship with table PermitNumbers. I created a single query that combines all the data so that I could permform a mail merge in Word, and the data in the query looks like the example I provided below. Yes, I can create a report in Access that will look like the example I gave below. The problem is that I can't use an Access report because my users have to be able to add all kinds of text to the report. In other words, the report needs to be totally editable. Any help you can provide would be greatly appreciated because I am totally stuck. "Peter Jamieson" wrote: It seems to me that there are potentially at least 2 problems he a. Word mailmerge is pretty bad at "one-to-many" stuff to begin with and b. it isn't completely clear from your example exactly what possibilities can occur in your data. I've tried to envisage what you might have been starting from but there seem to be several possibilities. and I suppose in the end it boils down to c. how is the output going to be used? For example, could you have one less record, e.g. Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 In that case, would you want Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ? since there would be no Pemit Number 676767 for 1234 Main St pertaining to Water Rights. If not, what would you want? At the moment it looks as if your primary sort sequence is by Issue, but beyond that, what? As for how to do it in Word, a. can you do it in Access reporting? b. if not, I'd suggest whatever the output needs to look like, it's probably going to need two steps: - sequence the information exactly as it needs to appear in your report (that's the hard part, IMO, because it seems to me to be hard to do using a query language such as SQL) - work out how to produce the report from that sequence. Peter Jamieson "Joshann" wrote in message ... I have several Access tables (that have one to many relationships) that I've combined into one query, and I need to create a fairly complex merge in Word with it. The table's data is like this with the first row being the column heads: Issue Program Facility PermitNumber Air Air Permits 1234 Main St 123456 Air Air Quality 1234 Main St 123456 Water Water Permits 1234 Main St. 121212 Water Water Permits 1234 Main St. 454545 Water Water Permits 1234 Main St. 676767 Water Water Rights 1234 Main St. 121212 Water Water Rights 1234 Main St. 454545 Water Water Rights 1234 Main St. 676767 Waste Waste Permits 1234 Main St. 898989 Waste Waste Permits 8888 1st St. 101010 The resultant merge document should look something like this: Issue: Air Programs: Air Permits Air Quality Facility: 1234 Main St. Permit Numbers: 123456 ------------------------------ Issue: Water Programs: Water Permits Water Rights Facility: 1234 Main St. Permit Numbers: 121212 454545 676767 ------------------------------ Issue: Waste Programs: Waste Permits Facility: 1234 Main St. Permit Numbers: 898989 Facility: 8888 1st St. Permit Numbers: 101010 I have seen how to do a compound merge where there are only two columns in the query, but I can't figure out how to do it with this many columns. Can anyone help with this? |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| complicated mail merge | Mailmerge | |||
| Complicated (to me) merge | Mailmerge | |||
| Some complicated merge codes | Microsoft Word Help | |||
| mail merge complicated | Mailmerge | |||
| Complicated mail merge with html coding | New Users | |||
Linear Mode
