I am creating a query of our data warehouse using Brio, of customer records and items purchased, then exporting the results to Excel. Because of the table structure, when a customer has purchased more than one item per order I end up with as many line items for that customer as items ordered.
Example:
John Sample, soap, $4, order #1
John Sample, shampoo, $6, order #1
Jane Doe, hair brush, $2, order #2
Carey Elwes, mask, $3, order #3
etc.
So, I’m trying to create a mail merge in Word to send invoices to these customers. I only want to send 1 letter per order. Is there a way to tell Word to count Order #1 as one record even though it’s on two lines, and indicate that the item bought and price should go into different fields in the document?
If it’s possible, I have faith that there is a Doper out there who can tell me how to do it.
You are skipping a step. You need to manipulate the data to make every customer and order one record and only one, instead of many customers per order.
I would take the data and import from Brio to Access, then use Access to turn:
John Sample, soap, $4, order #1
John Sample, shampoo, $6, order #1
Into:
John Sample, order #1
Before exporting the data to Excel and proceeding with your mail merge.
Oh, feel free to bill Dr. Bose $55 per hour for my time.
(It took me 8.75 hours to write this email.)
Thanks, Debaser. It’s only a few hundred records, so I may just do it manually in Excel. I was hoping for an easier way. Oh well, 
Thanks, Debaser. It’s only a few hundred records, so I may just do it manually in Excel. I was hoping for an easier way. Oh well,
I guess you could send two letters (avoiding the need to do a new query) and just put them in one envelope??
Or doesn’t that help?
No, that won’t really help because the order total would be printed on both letters, which would seem to the customer that we were trying to collect double (or triple, or however many items they had) payment.
If you are comfortable enough with reports in MS Access, you can create the entire letter as a report with the data grouped together by order number. Then the name/address/body of the letter will only appear once (as part of the header) and the series of orders will be presented in the detail section of the report. The order total can appear once in the group footer.
Gp