Complicated Merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joshann - 17 Apr 2007 22:06 GMT

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 to this Message

Peter Jamieson - 17 Apr 2007 22:47 GMT

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
[quoted text clipped - 6 lines]
> 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
[quoted text clipped - 3 lines]
> 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

>I have several Access tables (that have one to many relationships) that
>I've
[quoted text clipped - 43 lines]
> Can
> anyone help with this?

Reply to this Message

Joshann - 17 Apr 2007 23:14 GMT

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.

> 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
[quoted text clipped - 89 lines]
> > Can
> > anyone help with this?

Reply to this Message

Peter Jamieson - 17 Apr 2007 23:49 GMT

> 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

> Thanks for your reply. I'm trying to create a report in Word that looks
> like
[quoted text clipped - 120 lines]
>> > Can
>> > anyone help with this?

Reply to this Message

Joshann - 18 Apr 2007 14:34 GMT

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.

> > Table Issues has a one to many relationship with table Programs.
> > Table Issues has a one to many relationship with table Facilities.
[quoted text clipped - 140 lines]
> >> > Can
> >> > anyone help with this?

Reply to this Message

Doug Robbins - Word MVP - 25 Apr 2007 19:02 GMT

Or just use a report in Access.

Signature

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

> Thanks for your help, but I've decided to create the document using VBA.
> I'm
[quoted text clipped - 162 lines]
>> >> > Can
>> >> > anyone help with this?

Reply to this Message