Mail merge using 2007 Word and Access 1

[ddickers]

I have a client that needs a mailmerge that:
sends a letter to each of their clients that have past due invoices. The problem I'm having is that for clients that have more than one outstanding invoice, the mail merge prints a separate letter for each invoice instead of including all the past due invoices on one letter.

I've google, but no joy found.

Any suggestions would be appreciated.

 

[Lilliabeth]

Is the source of the mail merge an Access query?

--Lilliabeth

 

[ddickers]

Yes. The source database is Access 2007, with a query designed to list the past due notices.

 

[macropod]

Hi ddickers,

What you're trying to do is typically called a Catalogue or Directory Mailmerge. To see how to do this kind of mailmerge, check out my tutorial at:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=731107

Do read the tutorial before trying to use the mailmerge document included with it.

Cheers


[MS MVP - Word]

 

[Lilliabeth]

I can't see the query, so hard to say, but it seems to me that you should go to the properties sheet for the query and set the Unique Values to No.

I mean, isn't the reason you have duplicate letters because you needlessly have duplicates in the underlying query?

--Lilliabeth

 

[ddickers]

No, the reason is that some companies have more than one invoice that is past due, so they should be listed on just one letter instead of creating multiple letters to the same company; one letter for each past due invoice.

That is why I need to learn how to write the underlying logic in the mailmerge document to determine if the next record belongs on the existing letter, or if it needs it's own letter.

Thanks for the link to your tutorial. I'm going to check it out now.

Don

 

[ddickers]

The main mailmerge document was empty, so I didn't see any code in it.

In your instruction document, I looked at the code you inserted, but I didn't see any NEXT or NEXT RECORD, so in my example, I still get one record per page instead of combining several records for each company.

In my access database, I do have it sorted by companynumber, but how does the key field know which field is the key?

Thanks
Don

 

[macropod]

Hi Don,

The main mailmerge document was empty, so I didn't see any code in it.

That's why I stressed the need to read the tutorial. Please read it again - the field coding is in it, not in the main mailmerge document.

The idea is that you use the tutorial (from which you can copy the field coding) and associated mailmerge main document (into which you can paste the field coding) to decide which output format you're after, then you can copy the corresponding field code to your own mailmerge document and simply modify the field names and formatting to suit your own requirements.

Cheers

[MS MVP - Word]

 

[ddickers]

I copied the code that you provided for sorting on city, with page breaks for each city. However, I'm getting page breaks after each record instead of after only the key change.

The results look similar to the following for those companies that have more than one invoice:

Company Name
Company Address
Company City, state, zip

Attn: Accounts payable:
Invoice Amount Date
40916 3966.04 11/10/2008
-------page break-------------------------------

Attn: Accounts payable:
Invoice Amount Date
40916 3966.04 11/10/2008
-------page break-------------------------------

Attn: Accounts payable:
Invoice Amount Date
40916 3966.04 11/10/2008
-------page break-------------------------------


I hope that you get the idea of what is happening. I tried to copy the code from the word document, but it wouldn't copy in coded form.

Thanks
Don

 

[macropod]

Hi Don,

I tried to copy the code from the word document, but it wouldn't copy in coded form.

I suspect you've made an error in replicating the field code and/or the designation of the key field. It could be something as simple as mis-typing a field name. Without seeing your field coding and knowing something of the fields (and their names) in your data source, it's impossible to say.

There's a macro you can use to extract the field coding for pasting as plain text into forums like Tek-Tips here:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=320421

Note, though, that copying the field code from the tutorial should be no more difficult than for any other document content. Editing the field content to adjust the names & formatting to suit your needs requires little more than using Alt-F9 to expose the coding.

Cheers

[MS MVP - Word]

 

[ddickers]

Thanks to your advice and tutorial, I was able to solve the problem. I appreciate you helping me out and staying with me.

As it turned out, I had two specific problems that kept the mail-merge from performing the way you designed it. One was, the document had errantly developed two pages which I never noticed until finally I caught it. I'm sure that was causing all my havoc with the odd page breaks that didn't make sense.

The second issue was the way I was handling the key field, and the decision to define the sort on the query under the "edit source database" or whatever it is called.

Thanks Again!
Don