Advanced Word 2003 Mail Merge: Combining Records

Sometimes, we want to combine several records in a single letter using Word mail merge. For example, a library might want to acknowledge the receipt of several books from the same lender in a single letter. Simplified, the data source of titles received might look (in part) like this:

*Donor*	*Title*
Donor A	Title One
Donor A	Title Two
Donor A	Title Three
Donor B	Book One
Donor B	Book Two
Donor C	One Volume
Donor D	Another Text
Donor F	First of Many Books
Donor F	Second of Many Books
Donor F	Third of Many Books
Donor F	Last of Many Books

However, when Word mail merges from this data, it creates a separate letter for each record. It would be preferable to send one letter to “Donor A” for three titles, one letter to “Donor B” for two books, and so on.

Solution

Since an ordinary letter merge creates a new letter for each record, we merge to a directory, to combine multiple records on the same page.

Using a combination of IF and SET fields, we instruct Word to begin a new letter (on a new page) only when the Donor field changes.

Note: It is also possible to use the DATABASE field to insert the result of an Access database query into a Word document. However, this requires the design of a database SQL select statement, and the results are inserted in a table formatted automatically by Word.

Using Word Fields

Word fields let you insert field code into a Word document. Word fields do not appear in the printed document. When Word evaluates a field, it performs whatever instruction the field code specifies. This can be as simple as inserting the date or the current page number, or as complex as an If… then… else field that will test for a condition (for example, if an address is in the USA or not), and insert alternative text based on the outcome of the test (for example, insert the Country field only if the mailing address lies outside the USA).

Viewing Word Fields

Word fields are not normally visible in the Word document. Word fields do not print; only the text they specify will print. By default, text inserted by a Word field appears highlighted in grey when selected, or when the insertion point is inside the field.

To view field codes:

1. From the Tools menu, select Options. Click the View tab, and check Field codes.

2. Press Alt + F9.

Inserting a Word Field

Word field codes always appear between a pair of braces “{ }” called field placeholders.

To insert a pair of field placeholders into a Word document, press Ctrl + F9.

You can also nest certain fields by inserting one field inside another.

Make sure that field code always appears between the field placeholders.

Procedure

Open a blank document and begin the mail merge as you would normally. From the Tools menu, select Letters and Mailings, then Mail Merge.

In the Mail Merge task pane, select Directory and click Next: Starting Document.

Select your list of recipients (Word table, Excel list, Access database, and so on) and continue to setting up the main document.

In the main document, you must insert your field code, rather than inserting mail merge fields and normal text as usual.

The following sample field code illustrates a letter that acknowledges receipt of several titles at once. This field code must be inserted in field placeholders. You cannot copy and paste this example directly into your own document. When you enter this code, use the appropriate fields from your own data source.

Also note the position of the page break. Paragraph markers are made visible here to show the layout of the fields.

{ IF { MERGESEQ } = 1 “¶

{ MERGEFIELD “Donor” }¶

{ MERGEFIELD “Address” }¶

{ MERGEFIELD “Postal_Code” }¶

Dear, { MERGEFIELD “Donor” },¶

Thank you for the following titles: ¶

“ “” }{ SET Donor1 { MERGEFIELD “Donor” } }¶

{ If { Donor1 } <> { Donor2 } “¶

Yours truly, ¶

---------------------- Page Break ----------------------

{ MERGEFIELD “Donor” }¶

{ MERGEFIELD “Address” }¶

{ MERGEFIELD “Postal_Code” }¶

Dear, { MERGEFIELD “Donor” },¶

Thank you for the following titles: ¶

{ MERGEFIELD “Title” }” “{MERGEFIELD “Title” }” }{ SET Donor2 { MERGEFIELD “Donor” } }

What the Field Code Does

Code

Function

{ IF { Mergeseq } = 1 “

{ MERGEFIELD “Donor” }

{ MERGEFIELD “Address” }

{ MERGEFIELD “Postal Code” }

Dear, { MERGEFIELD “Donor” },

Thank you for the following titles:

” “” }

This field code sets up the first letter for the first record in the data source.

The IF function tests for the first record in the merge sequence using the Mergeseq (merge sequence) field. If this is the first record ({ Mergeseq } = 1), Word inserts the text typed between the following quote marks. This is everything that appears at the start of the letter (address, salutation, letter text, and so on), up to the first title field.

If this is not the first record in the merge, and for all subsequent records, this field prints nothing. The two quote marks with no space between them represent null.

{ SET Donor1 { MERGEFIELD “Donor” } }

The SET function sets the value of a bookmark, Donor1, to the value of the Donor field in the current record.

This bookmark keeps track of the current donor, to check for when the donor changes.

{ IF { Donor1 } <> { Donor2 } “

Yours truly,

------- Page Break ---------

{ MERGEFIELD “Donor” }

{ MERGEFIELD “Address” }

{ MERGEFIELD “Postal Code” }

Dear, { MERGEFIELD “Donor” },

Thank you for the following titles:

{ MERGEFIELD “Title” }” “{MERGEFIELD “Title” }” }

The IF function tests for a change in the Donor field, by comparing the bookmark Donor1 with the bookmark Donor2.

If the donor has changed (Donor1 is not equal to Donor2), Word inserts the text that ends the letter, including a signature line, and inserts a page break to begin a new letter.

The text inserted after the page break must exactly match the text of the first part of the first letter.

If the donor has not changed (Donor1 is the same as Donor2 or null), Word continues with the list of titles received and inserts the next title.

{ SET Donor2 { MERGEFIELD “Donor” } }

Sets the bookmark Donor2 to the value of the current Donor field.

When Word moves to the next record, the value of the Donor field for that record is compared to this, and if the Donor has changed, Word begins a new letter (as above).

Completing and Viewing the Mail Merge

Once you have entered your field code, you can only view you main document by revealing the Word fields. If the field codes are hidden, your document will appear virtually blank!

Unlike a normal Word mail merge, you cannot accurately preview the results. To generate and test your mail merge letters, merge to a new document and view (and edit) the results there.

Finally, because there is no way of testing when the last record has printed, the last letter of your mail merge will omit any text following the last merge field, including the signature line. To complete your merged letters, simply copy and paste this from the preceding page.

Technical References

For more information on Word fields, refer to Word Help.

For more information on the field codes used to merge multiple records, go to:

http://support.microsoft.com/?scid=http%3a%2f%2fwww.support.microsoft.com%2fkb%2f294686%2f