Mail Merge listing data within merged letters

[PHaydock]

Hi
Application is mail merge using a database table, sending letters to contractors. Within each letter I want to list multiple details which I am calling a nested data list (eg all invoices and amounts) then do some more merge fields for conractor1. Then start contractor2, repeat the exercise for his invoices, then contractor3, etc.

I have spent hours trying to make it work. I don't want blank rows, and each contractor has a different number of invoices. I have tried using SET... if a invoice# is not blank, print the details, but if blank print nothing... I can get a nested list but then the no more merged data... i.e. can't get contractor2 to print (not even his name.)
Am I making sense?

TKS
PAUL

 

[SkipVought]

Paul,

If I were doing an Invoice-like mail merge, I'd use Excel without a doubt. I don't know what your database looks like, but mine would be normailzed; meaning that I'd have a table containing Customer-related info like names, addresses, contacts; another table containing Order Summary like CustomerID, OrderID, Order Date etc.; then another table containing the Order Detail containing items like OrderID, ItemID, Order Qty, Total, etc and another table with my Catalog with ItemID, Item Description, Unit Price, Unit of Measure, Last Stock Date etc.

Of course, your data would look entirely different.

With a set of table like this I could drive my process from a list of CustomerID/OrderID, using MS Query to return each segement of data form each table associated with a particular order and from that, populate my Invoice. Word Mail-Merge cannot accomodate this level of complexity.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHaydock]

Skip,
Thanks. Unfortunately EXCEL is not an immediate option. Cystal is, but if Word can't do it (and I would like to think it can) we can send srings of data out of the accounting software.

Its not actually invoicing we are doing - that was a simple example. Its Contract Management processes, using a professional Contract Management system which we have several large government departments using.
Thanks again.

 

[BenRowe]

To me, it sounds like you have a merge letter that needs to have a ResultSet inserted into it. If this is that case, then in Insert - Field - MailMerge there is a field for Database which will create a table from a SQL query.