Mail Merge with a many-to-one report/query

  • Thread starter Thread starter jdgretz
  • Start date Start date
J

jdgretz

Here's the problem:

Using Office 2003 (don't ask) a company needs to send out letters to clients
showing invoices generated to their organizations. An Access Report built
from a Query shows the information in the correct format, but it's not a
Mail-Merged letter. The reports looks like this:

Master Company Record 1, Contact, Company ID, etc.
Division name
Division invoice number
Division invoice number
Division invoice number
:
Division 2 name
Division 2 invoice number
:
Division 2 invoice number

Master Company Record 2, Contact, Email Addr, Org ID Number.
Division name
Division invoice number
Division invoice number
Division invoice number
:
Division 2 name
Division 2 invoice number
:
Division 2 invoice number

Works fine as an Access Report - also the report exports correctly to a
grouped Excel spreadsheet, but with each invoice on a separate line
mail-merge only picks up the first invoice instance. Using the Access query,
I get a separate letter for each invoice - not what is desired. The wish is
to send a single letter to each Corporation or master company showing all the
invoices for that master record.

Looking at the data from the report as exported to Excel I get:

Line 1 - Header Information
Line 2 - Col A - Master Co. Record; Col B - Contact; Col C - Email Addr. Col
D - Org ID Number
Line 3 - Col E - Division Name
Line 4 - Col F - First Invoice Number
Line 5 - Col F - Next Invoice Number
:
:
Line n - Col E - Next Division Name
Line n+1 - Col F - First invoice number for this division

Oh yes, one more "little" detail...

Since some companies have dozens of invoices, I'd like the section with the
invoice numbers to be in multiple columns so as not to produce a page or two
of just invoice numbers. That should be easy once I get the merge to print
the multiple invoice numbers.


Ideas??

John
 
I would try build a solution using automation to Word. This would involve
opening a Word template document and then using code to loop through your
records and add text to the document.

You might want to try post a similar question to a Word news group and/or
google it.
 
Back
Top