Mail Merge from Two Tables (2003)

[Lou Sander]

This might be challenging. I have tblAllLocated, which contains information about our members. For several years I've used it as the data source for a Microsoft Word mail merge document that I print out every spring to use in fundraising. The mail merge document is quite intricate, with lots of IFs in various fields, carefully-adjusted field sizes, etc. After four years of polishing and bug-killing, it works very well with our data.

This year I have a new table, tblDutyStations, which contains from one to ten former duty stations for each member for whom we have that information. In contains records for about 500 of our 1,300 members. The two tables are related through the Handle field; the Handle is similar to a customer number. tblDutyStations has just two fields: Handle and DutyStation. Multiple duty stations are handled by putting them into multiple records.

I want to add duty stations to our annual printout this year, preferably in a 2 column X 5 row table at the bottom. This will require putting data from two tables into one mail merge document, possibly with some sort of intermediate step to combine the data into an Access query or Excel worksheet (but I'm just guessing about the feasibility of that).

I'd like to keep things as a Word mail merge document if possible, but I COULD convert everything to an Access report. If I go to a report, I give up all the accumulated experience and bug-free nature of the well-proven mail merge document.

Any suggestions?

[HansV]

Option 1:

You can create a query in the database based on tblAllLocated and tblDutyStations, joined on Handle. If *each* record in tblAllLocated has at least one corresponding record in tblDutyStations, you can use the standard join, but if there are some members without a corresponding record in tblDutyStations, you must double click the join line in the query design window and select the option to return *all* records from tblAllLocated.
You can then use this query as data source for the merge. But since you now have multiple records per member, you must put Word through some hoops to prevent multiple letters per member. See MSKB article How to use mail merge to create a list sorted by category in Word.

Option 2:

You can create a query in the database based on tblAllLocated, and add a calculated field that concatenates the duty stations for a member.
First, copy the Concat function attached to <post:=301,393>post 301,393</post:> into a standard module.
Then, use the following expression in your query:

DutyStations: Concat("tblDutyStations", "DutyStation", "Handle=" & [Handle])

This assumes that Handle is numeric; if it is a text field, use

DutyStations: Concat("tblDutyStations", "DutyStation", "Handle=" & Chr(34) & [Handle] & Chr(34))

Add the fields from tblAllLocated. Save this query and use it as data source for the mail merge. You'll have an extra field DutyStations.

[Lou Sander]

Thanks! I'll experiment and let you know.

I thought it might be impossible.