CNFS IQ - How to Use Query Results as a Data Source for a Mail Merge Document

This topic shows you how to generate a query and use the results as a data source for a mail merge document.


-

Mail merge is a process for creating formal announcements, form letters, campaign correspondence, or any other types of documents that you want to produce for mass mailing purposes yet personalize to make more appealing to each recipient. Mail merge is great for simplifying the creation of repetitive documents, such as mailing labels and address lists.

-

What's Needed?

To perform a mail merge, you need the following:

        Main Document

The main document contains fixed content. "Fixed" means that the information does not change. Fixed content can consist of text you create from within the Mail Merge application. Alternatively, you can pull text from an existing template (for example, one in your Global or My Templates folder) or copy from a text document stored on your local computer. The fixed content can even include a graphic, such as a photography or your church logo.

        Data source

Technically, the data source is your database, but it is your query results that will transfer the actual data values from columns to the merge fields in your document. For this reason, you must make sure that the query contains the columns you need to deliver to the merged document (for example, Members Title, Members Last Name, and Members.Contact Info Home Address 1).

When you run Mail Merge, the process outputs a document for each recipient using the fixed text content and substituting the data values from the columns in your query.

-

Using Query Results as a Mail Merge Data Source

This section shows you how to create a mail merge document that uses a query as a data source. We will use a thank-you letter as our sample document to help you understand the process.

-

Sample Document

Suppose that you want to create a thank-you letter to express your appreciation to givers who gave a donation to your school building fund. An example of a thank-you letter you might create is shown in the following illustration:

In the example letter in the illustration below, we inserted placeholder text (the text in all caps and highlighted in yellow). We will use the placeholders as a guide for where to insert the merge fields (query columns) into the document.

As the previous illustration shows, our sample letter contains both variable fields and fixed text. For illustration purposes only, the seven variable fields (for example, TITLE and TOTAL AMOUNT OF PLEDGE) in the sample letter are shown in all caps and highlighted in yellow. These are the placeholders (merge fields) that indicate where to insert information unique to each recipient.

IMPORTANT

The placeholders correspond to the columns that you must have in your query. Later, when you select the merge fields to insert in your document, only the columns included in your query are available for you to select.

During the merge, the unique information in the columns will replace the placeholder fields in each recipient's copy of the letter. The fixed text, on the other hand, will remain the same for each person you send the letter to.

Overview of the Process of Creating a Link between a Query and a Word Document

The mail merge process entails these steps:

  1. Set up and run a query.

The query is the data source. When setting up the query, you must select the columns and apply conditions in the query to generate the information you need to be merged into the document.

  1. Open Mail Merge and create the main document.

In this step, you will open a blank document and enter your fixed text and merge fields. Fixed text is custom text that is the same for each version of the document, for example, the body of a letter and closing words, such as "Sincerely" and "Warm Regards." Fixed text can also include an inserted graphic, such as your church logo or a photograph. Merge fields are placeholders that represent real information, such as the recipient's actual name and address.

  1. Add the merge fields (query columns) into the main document.

The merge fields in your document correspond to columns in your query. In this step, you will replace the merge fields in the document with the columns that contain the data. During the merge, the system will extract the column's actual data and insert it into the merge field.  

  1. Merge the query data into the main document and finalize the merge.

In this last step, you will perform the mail merge. The system merges your query results into each individual document and creates a single file. You can then preview each document before saving the file to your computer.

Detailed instructions for each step follow.

-

Step 1. Set Up and Run the Query

To use a query as a data source, complete the following:

  1. Do one of the following:
  • Select an existing query from the Recent Queries list or My Queries list to use the data source.

OR

  • Build the query the way you typically do by choosing columns and conditions from the Column Picker.

For the sample thank-you letter presented earlier, these seven columns must be included in the query:

  • Members > Contact Info > Home Address 1
  • Members  > Contact Info > Home City
  • Members > Contact Info > Home State
  • Members > Contact Info > Home State
  • Members > Title
  • Members > Full Name
  • Pledges > Total Pledge Amount
  1. Select the Unique Records Only checkbox so that the query output contains no duplicate records.
  2. Click  to run the query.

The results are displayed in the Query Results panel.

  1. If desired, click  to save the query.
  2. You are now ready to create the main document. Go to Step 2. Open Mail Merge and Create the Main Document.

-

Step 2. Open Mail Merge and Create the Main Document

  1. At the top of the Query Results panel, click . From the dropdown menu, select the Mail Merge option.

The Mail Merge window opens:

  1. Select the create a new one link to open a blank document.
  2. Do one of the following:
  • Type the content of your document directly into the blank document text area.
  • If you have a text document saved, open it. Then, copy and paste the text from that document into the blank document text area.

In the following example, we saved the text of our thank-you letter, copied it, and then pasted it into the document text area:

 

  1. You can now add the merge fields (query columns) into the main document. Go to Step 3. Add the Merge Fields Query Columns to the Document.

-

Step 3. Add the Merge Fields (Query Columns) to the Document

  1. Select the Mail Merge tab, located at the top of the document window.

  2. Add the merge fields to your document. To do this, complete the following:

a. Select the text where you want to insert the first merge field (query column).

The system highlights the selected text. Using our thank-you letter example, we start by selecting the first merge field, GIVER'S STREET ADDRESS, as shown below:

b. Click .

The Fields menu is displayed. The menu lists the columns included in your query. Using the thank-you letter example, you see the seven columns contained in the query:

c. Select the column that you want to replace the selected text.

The system replaces the selected text with the column you selected.

For example, selecting Members_Contact Info Home Address 1 from the menu replaces GIVER'S STREET ADDRESS. The result looks like this:

d. Repeat Step 2a - 2c until all of the merged fields in your document are replaced by the correct columns.

Using our example thank-you letter, after replacing the merge fields with the column selections, the document looks like this:

3. Proof the document If necessary, edit the content and your merged field selections. When done making changes, select the File tab. Then, click  to save the document.

-

Step 4. Merge the Query Data into the Main Document

In this step, you will merge the actual data derived from your query results into the merge fields in your main document.

  1. Select the Mail Merge tab, located at the top of the document window.
  2. Click .

The merge fields in your document are replaced with data query. The first recipient's copy is displayed, as shown in the following illustration:

Note that the details are personalized for the recipient (for example, the name, address, and the total pledged amount). The remainder of the letter contains the common content.

  1. If you have multiple recipients, click  (Next Record) to view the copy for the next recipient.
  2. Repeat Step 3 until you finish previewing each recipient's copy of the letter, making sure that each one is correct.
  3. Finalize the merge by completing these steps:

a. On the Mail Merge tab, click  (Merge to File) to open the Merge to File window:

b. From the Merge mode dropdown list, select one of these options:

  • New Section: specifies that the next merged fields start in a new section or page. If you have headers and footers in your document, select this option. If you are creating a plain document, such as a form letter, select this option to provide a page break between each recipient's copy of the document.
  • New Paragraph: specifies that the next merged fields start with a new paragraph.
  • Join Tables: joins rows, resulting from a mail merge involving a table with merged fields, into a whole table.

c. From the Choose file type dropdown list, select the document's file type. Mail Merge supports these file types:

  • .doc and .docx: a Microsoft Word document file. This file is readable by Microsoft Word only.
  • .rtf: Rich Text Format file. This file is readable by most word processing programs in different operating environments and with different operating systems.
  • .txt: a plain text file. This file is readable by any text editing or word processing program.
  1. Click  to close the Merge to File window.

The system downloads the file to your computer. The single file contains all of the individual documents or letters. You can print the

-

Printing a Mail Merge Document

  1. Navigate to the location on your computer where you downloaded the mail merge document.

  2. Open the document.

  3. From the File menu, select the Print option.

All of your merge documents are in a single file. Each copy is printed on a separate page.

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.