Microsoft KB Archive/318112

= How to use addresses from an Access 2002 database to create labels in Word 2002 =

Article ID: 318112

Article Last Modified on 9/25/2006

-

APPLIES TO


 * Microsoft Word 2002 Standard Edition
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q318112



SUMMARY
This article explains how to use the Mail Merge feature in Word to create and print labels using data from an Access database. The article contains instructions using the Customers Table in the Northwinds sample database that is supplied with Access.

When you use the Microsoft Word 2002 mail merge feature, Word merges a &quot;main document&quot; with a &quot;recipient list&quot; to generate a set of &quot;output documents&quot;:
 * The main document contains the basic text that is the same in all of the output documents. It may contain a letterhead, text, and instructions in &quot;merge fields&quot; for inserting text (such as recipient names and addresses) that varies from one output document to another.
 * The recipient list is a database-for example, a Microsoft Access 2002 database file or a Microsoft Excel 2002 workbook-that contains the data that is to be merged into the output documents. This database is typically a list of names, addresses, and phone numbers.
 * The output documents are the result of the mail merge. The text in an output document can be the same in all output documents, but you can apply formatting to specific documents.

Step 1: Prepare the Access Database
Before you proceed with the Mail Merge Wizard, decide how you want to use your Access database for the label in Word 2002. There are several methods that you can use:
 * Use an Access Table or Query In a Word Document
 * Export an Access Table or Query Directly Into a Word Document
 * Export Your Data from Microsoft Access as a Word Data Source
 * Copy, Paste Microsoft Access Data as a Word Data Source

Use an Access Table or Query In a Word Document
You can use an Access table or query directly from a mail merge in Word, without making any additional modifications to the table or query. To use a Microsoft Access query or table as a Word data source, continue with the steps outlined in the &quot;Step 2: Set Up the Main Document&quot; section of this article.

Export an Access Table or Query Directly Into a Word Document
You can use Access to embed data from an Access table or query directly into a Microsoft Word document. To embed data from an Access database in a Word document, follow these steps:
 * 1) Open your database, and select the table or query containing the data that you want to embed in a Word document.
 * 2) On the Tools menu click Office Links, and then click Merge It With MS Word (or click Merge It With MS Word on the Office Links toolbar). The Microsoft Word Mail Merge Wizard starts.
 * 3) Select the option to link to an existing Word document or the option to create a new document. If you select the option to link to an existing Word document, the wizard will ask for the document location. When you finish, click OK.
 * 4) The wizard starts Word and activates a mail merge link to your table or query.
 * 5) Continue on with the second step under the &quot;Step 2: Set Up the Main Document&quot; section of this article.

Export Your Data from Microsoft Access as a Word Data Source
To export and use your data from Microsoft Access as a Word data source, follow these steps:  In Microsoft Access, open your database table or query. On the File menu, click Export. In the Export Table or Export Query dialog box, follow these steps:  In the Save as type box, click Microsoft Word Merge (*.txt). In the File name box, type a name for your exported file. In the Save in box, click the folder in which you want to save the file. </li> Click Export All. Microsoft Access now exports your table or query result as a tab-delimited text file.</li> Close your database, and then quit Microsoft Access.</li> Continue with the steps outlined in the &quot;Step 2: Set Up the Main Document&quot; section of this article. When you reach the &quot;Step 3: Specify the Microsoft Access Data Source&quot; section of this article, select the exported tab-delimited text file to continue your Word mail merge or to insert the database file as usual.</li></ol>

Copy, Paste Microsoft Access Data as a Word Data Source
To copy, paste, and use Microsoft Access data as a Word data source, follow these steps:
 * 1) Open your Microsoft Access database table or query.
 * 2) On the Edit menu, click Select All Records.
 * 3) On the Edit menu, click Copy.
 * 4) Open a new, blank document in Microsoft Word.
 * 5) Use one of the following methods.

<ul> Method 1: Save Data as a Table  On the Edit menu, click Paste Special. In the Paste Special dialog box, under As, click Formatted Text (RTF). The information from your Microsoft Access table or query is pasted into a Word table.</li> Save this new file as a Word document. This new file is your new Word data source.</li> Continue with the steps outlined in the &quot;Step 2: Set Up the Main Document&quot; section of this article. When you reach the &quot;Step 3: Specify the Microsoft Access Data Source&quot; section of this article, select the exported tab-delimited text file to continue your Word mail merge or to insert the database file as usual.</li></ol>

</li> Method 2: Save Data in Tab-Delimited Format  On the Edit menu, click Paste Special.</li> In the Paste Special dialog box, click Unformatted Text, and then click OK. The information from your Microsoft Access table or query is pasted into your Word document as tab-delimited text.</li> On the File menu, click Save As.</li> In the Save As dialog box, click Plain Text in the Save as type box.</li> In the File name box, type a name for your exported file.</li> In the Save in box, click the folder in which you want to save the file, and then click Save.</li> <li>In the File Conversion dialog box, under Text encoding, click Windows (Default), and then click OK.</li> <li>Close your Word data source document.</li> <li>Continue with the steps outlined in the &quot;Step 2: Set Up the Main Document&quot; section of this article. When you reach the &quot;Step 3: Specify the Microsoft Access Data Source&quot; section of this article, select the Word document containing your table file to continue your Word mail merge.</li></ol> </li></ul>

Step 2: Set Up the Main Document
<ol> <li>Start Microsoft Word 2002, and on the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.</li> <li>Under Select document type, click Labels.

The active document becomes the main document (the document that contains the text and graphics that are the same for each version of the merged document, for example, the mailing address on a label).</li> <li>Click Next: Starting document.</li> <li>Do one of the following: <ul> <li>Create a new sheet of labels <ol style="list-style-type: lower-alpha;"> <li>Click Change document layout.</li> <li>Click Label options.</li> <li>In the Label Options dialog box, select the options you want, such as the label type and size, and then click OK.

For Help on an option, click the question mark, and then click the option.</li></ol> </li> <li>Start with an existing sheet of labels <ol style="list-style-type: lower-alpha;"> <li>Click Start from existing document.</li> <li>In the Start from existing box, select the document you want, and then click Open.</li> <li>If you do not see the document, click More files, and then click Open. In the Open dialog box, locate the document you want, and then click Open.

Microsoft Word displays the document in the document window. If you decide to use a different label document instead, Click Start from existing document, and then select a different document. If you want to change the layout, click Change document layout and then click Label options.</li></ol> </li></ul> </li> <li>Click Next: Select recipients.</li></ol>

Step 3: Specify the Microsoft Access Data Source
NOTE: See the &quot;Step 1: Prepare the Access Database&quot; section of this article for alternate methods of preparing an Access database for use with the Mail Merge. <ol> <li>Under Select recipients, click Use an existing list.</li> <li>Click Browse. By default, Microsoft Word opens the My Data Sources folder.</li> <li>In the Select Data Source dialog box, locate and click the Microsoft Access Database that you want to use, then click Open. For this example, you can use the Customers Table in the Northwinds Sample database that is included with Access. By default, the Northwinds Sample database is installed in the following location:

&quot;C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb&quot;

</li> <li>In the Select Table dialog box, select the Table or Query that you want to use. For this example, select the Customers Table, and then click OK.</li> <li>All of the entries in the data source appear in the Mail Merge Recipients dialog box, where you can refine the list of recipients to include in the merge.</li></ol>

Step 4: Select the Recipients
<ol> <li>In the Mail Merge Recipients dialog box, select the recipients you want to include. To do this, do any of the following: <ul> <li>Use the check boxes to designate recipients.

This method is most useful if your list is short. Select the check boxes next to the recipients you want to include, and clear the ones next to the recipients you want to exclude.

NOTE: If you know you want to include most of the list in your merge, click Select All and then clear particular records. Similarly, if you want to include only a few records in the list, click Clear All, and then select the records you want.</li> <li>Sort items in the list

This is useful if you want to see items in alphabetical or numeric order. Click the column heading of the item you want to sort by. For example, if you want to display the list alphabetically by last name, click the Last Name column heading.</li> <li>Filter items in the list

This is useful if the list contains records that you know you do not want to see or include in the merge. After you have filtered the list, you can use the check boxes to include and exclude records as described in the previous section. To filter the list, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Click the arrow next to the column heading of the item you want to filter by.</li> <li>Click any of the following: <ul> <li>(Blanks) displays all the records in which the corresponding field is blank.</li> <li>(Nonblanks) displays all the records in which the corresponding field contains information.</li> <li>If your data source contains records that share the same information, and there are ten or fewer unique values in the column, you can filter by specific information. For example, if there are multiple addresses that list Australia as the country/region, you can filter on Australia.</li></ul> </li></ol> </li> <li>The Mail Merge Recipients dialog box displays only the designated records. To display all the records again, click (All).</li></ul>

Notes: <ul> <li>For advanced sorting and filtering, click the arrow next to any column name, and then click (Advanced). Use the Filter Records and Sort Records tabs to set up the sorting or filtering query you want.</li> <li>If you have installed address validation software, you can click Validate in the Mail Merge Recipients dialog box to validate your recipients' addresses.</li></ul> </li> <li>:For this example, click Clear All, and then select the first four customers on the list.</li> <li>:Click OK to return to the Mail Merge Wizard. Microsoft Word will use the recipients you designated for the merge.</li> <li>:Click Next: Arrange your labels.</li></ol>

back to the top

Step 5: Arrange the Content of Your Labels
If you have not already done so, in the main document, type the text that you want to appear in every label.

Insert Merge Fields
Insert merge fields where you want to merge names, addresses, and other information from the data source. To insert merge fields, follow these steps: <ol> <li>In the main document, click where you want to insert the field.</li> <li>Insert any of the following: <ul> <li>Address block with name, address, and other information <ol style="list-style-type: lower-alpha;"> <li>Click Address block.</li> <li>In the Insert Address Block dialog box, select the address elements you want to include and the formats you want, and then click OK. For Help on an option, click the question mark, and then click the option.

</li> <li>For Help on an option, click the question mark, and then click the option. If the Match Fields dialog box appears, Microsoft Word may not have been able to find some of the information it needs for the address block. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.</li></ol> </li> <li>Greeting line <ol style="list-style-type: lower-alpha;"> <li>Click Greeting line.</li> <li>Select the greeting line format, which includes the salutation, name format, and following punctuation.</li> <li>Select the text you want to appear in cases where Microsoft Word cannot interpret the recipient's name, for example, when the data source contains no first or last name for a recipient, but only a company name.</li> <li>Click OK.</li> <li>If the Match Fields dialog box appears, Word may not have been able to find some of the information it needs for the greeting line. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.</li></ol> </li> <li>Other fields of information <ol style="list-style-type: lower-alpha;"> <li>Click More items.</li> <li>Do one of the following: <ul> <li>To select from address fields that will automatically map to corresponding fields in your data source, even if the data source's fields do not have the same name as your fields, click Address Fields.</li> <li>To select from fields that always take data directly from a column in a database, click Database Fields.</li></ul> </li> <li>In the Fields box, click the field you want.</li> <li>Click Insert, and then click Close.</li> <li>If the Match Fields dialog box appears, Microsoft Word may not have been able to find some of the information it needs to insert the field. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.

Note: If you insert a field from the Database Fields list, and then later switch to a data source that does not have a column with the same name, Word cannot insert that field information into the merged document.</li></ol> </li> <li>Electronic postage To add electronic postage, you must first install an electronic postage program, such as one you purchased from a service on the World Wide Web. To use electronic postage, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Click Electronic postage.

If you do not have an electronic postage program installed, Microsoft Word prompts you to install one and offers to connect to the Microsoft Office Web site.</li> <li>Insert the postage according to the program's instructions.</li></ol> </li> <li>To add electronic postage, you must first install an electronic postage program, such as one you purchased from a service on the World Wide Web. To use electronic postage, follow these steps: Postal bar code You must select a letter or envelope type that supports the POSTNET bar code. To use the Postal bar code, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Click Postal bar code.</li> <li>In the Insert Postal Bar Code dialog box, select the appropriate address fields. Note: The Postal bar code option only appears if you are using the U.S. language version of Microsoft Word.</li> <li>Repeat steps a and b for all the fields you want to insert. Notes: <ul> <li>You cannot type merge field characters (<<>>) or insert them by using the Symbol command on the Insert menu.</li> <li>If the merge fields appear inside braces, such as { MERGEFIELD City }, then Microsoft Word is displaying field codes instead of field results. This doesn't affect the merge, but if you want to display the results instead, right-click the field code, and then click Toggle Field Codes on the shortcut menu.</li></ul> </li></ol> </li></ul>

NOTE: You can also use the Mail Merge toolbar to insert merge fields, work with your mail merge main document, or to run a mail merge. To display the Mail Merge toolbar, on the Tools menu, point to Letters And Mailings, and then click Show Mail Merge Toolbar. The Mail Merge toolbar provides additional commands not included in the Mail Merge wizard task panes. For example, you can use the Insert Word Field drop-down menu on the Mail Merge toolbar to insert Word fields for controlling the merge process (for example, an IF field that inserts text only if a particular merge field has a specified value). Or, you can click Check For Errors to have Word run the mail merge and report any errors contained in the main document.</li></ol>

Change the Format of the Merged Data
To format merged data, you must format the merge fields in the main document. Do not format the data in the data source, because its formatting is not retained when you merge the data into the document. To change the format of the merged data, follow these steps:
 * 1) In the main document, select the field containing the information you want to format, including the merge field characters (<<>>).
 * 2) On the Format menu, click Font, and then select the options you want.

Format by Using Field Codes
To control other aspects of formatting, press ALT+F9 to display field codes, and then add switches to the merge fields. When working with fields, a switch is special instruction that causes a specific action to occur. Generally, a switch is added to a field to modify a result.

For example:
 * 1) To display the number &quot;34987.89&quot; as &quot;$34,987.89,&quot; add the Numeric Picture switch (\#).
 * 2) To print client names in uppercase letters, add the Format switch (\*).
 * 3) To make sure that the merged information has the same font and point size you apply to the merge field, add the \* Charformat switch.

To copy the format and layout of the first label to all the other labels on the page, click Update all labels.

For this example, click the upper left corner of the page, insert the AddressBlock field, and then click Update all labels. Your first page should appear similar to the following: <<AddressBlock>>                 <<Next Record>><<AddressBlock>>   <<Next Record>><<AddressBlock>>

<<Next Record>><<AddressBlock>>  <<Next Record>><<AddressBlock>>   <<Next Record>><<AddressBlock>>

Step 6: Save the Document
After you have completed the arranging the main document and inserted the merge fields, you should save the document before proceeding.
 * 1) Click Save As on the File menu.
 * 2) Name the document, and then click Save.
 * 3) Click Next: Preview your labels.

Step 7: Preview the Labels and Fine-Tune the Recipient List
When the wizard displays the Step 5 Mail Merge task pane, it replaces each of the merge fields in the main document with the actual text from the first entry of the recipient list, so you can see how your first output document will look.

For example, using the sample Northwinds database, when you click Next: Preview your labels, the first page should appear similar to the following: <pre class="fixed_text">  Alfreds Futterkiste                   Ana Trujillo Emparedados y helados Obere Str. 57                        Avda. de la Constitucion 2222 Berlin 12209                         Mexico D.F. 05021 Antonio Moreno Taqueria              Around the Horn Mataderos 2312                       120 Hanover Sq. Mexico D.F. 05023                    London WA1 1DP To preview additional entries, do either of the following:
 * To preview the items in order, click the left or right arrow buttons.
 * To locate and preview a specific item, click Find a recipient, and then enter the search criteria in the Find Entry dialog box.

If you want to, fine-tune the recipient list. Do one of the following:
 * To change the list of recipients, click Edit recipient list, and then make your changes in the Mail Merge Recipients dialog box.

back to the top

Step 8: Complete the Merge
To complete the merge, do any of the following.

Personalize Individual Labels
To personalize individual items, you actually complete the merge, and then edit the information you want in the resulting merged document.
 * 1) Click Edit individual labels.
 * 2) In the Merge to New Document dialog box, select the records you want to merge.
 * 3) Click OK.

Microsoft Word creates and opens a new merged document. Your main document also remains open, and you can switch back to it if you want to make a change to all the documents.
 * 1) Scroll to the information you want to edit, and make your changes.
 * 2) Print or save the document just as you would any regular document.

back to the top

Print the Labels
To print the labels, do either of the following:
 * If you personalized the items and the merged document is active:
 * On the File menu, click Print.
 * Select the options you want.
 * If you want to print directly from the Mail Merge Wizard:
 * In Step 6 of the Mail Merge Wizard (Complete the merge), click Print.
 * In the Merge to Printer dialog box, do one of the following, and then click OK.
 * To print all the documents, click All.
 * To print the document that you see in the document window, click Current record.
 * To print a range of documents, click From, and then type the record numbers in the From and To boxes.
 * In the Print dialog box, select the options you want.

back to the top

Save the Merged Labels for Later Use
If you want to edit merged labels or save them for later use, you can collect them into a single document.
 * 1) Click Edit individual labels.
 * 2) In the Merge to a New Document dialog box, do one of the following, and then click OK.
 * 3) * To merge all the documents, click All.
 * 4) * To merge only the document that you see in the document window, click Current record.
 * 5) * To merge a range of documents, click From, and then type the record numbers in the From and To boxes.
 * 6) Microsoft Word opens a single new document that contains all the individual labels. You can then save the document for later use, just as you would any regular document.

back to the top

<div class="references_section">