Access with Mail Merge

[WiscCard]

I have a problem that there may not be an answer for.
I have a database in Access. In this database are two different tables.
One table contains customer information (name, address etc.) Each customer has an ID that is used as my primary key.
I have another table that contains their pricing on products. Each row of this table has the customer ID as my other table. Now, since each customer has multiple products, they could have as many as 20 rows of data, but all with the same customer ID.
What I need to do in to create a mail merge document that contains their name, address, etc. information from the first table, and include ALL their data from the 2nd table. I figured the best way to do this would be to run a query and create the mail merge from there. The problem is that when the query is ran, it matches the data fine, but only treats each product as a seperate entity.
I need the query (and after exporting, the mail merge) to include all products for the company, not just each row individually.
Any suggestions?

 

[Joe4]

So it sounds to me that with your Products table, instead of having multiple records for each ID, you need one record with all of the products. You may be able to accomplish that by creating a Cross Tab Query (see Access built-in Help for details on how to do that).

Then, you can link this CrossTab query to your Customer Table in a new query, and use this new query as your Data Source for your Mail Merge.

 

[SydneyGeek]

It's been a while since I had to do this but I think it's possible to set up the merge fields in Word so that you can get multiple lines for a single client. Check the Word help, and also go for a look over at TheOfficeExperts.com

An alternative (which doesn't give you Word's formatting ability but definitely does lay out the data correctly) is to create a report in Access with a sub report containing the "many" part.

Denis