Complicated Merge

[Joshann]

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?

 

[Peter Jamieson]

It seems to me that there are potentially at least 2 problems here:
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]

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]

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:

? 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]

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.

 

[Doug Robbins - Word MVP]

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