Home > Guides > Home & Office Computing > Microsoft Applications

Microsoft Office Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Using an Access Query for Mail Merge

Last updated Mar 26, 2004.

When my editor asked me about a problem she'd encountered, it occurred to me that this would be a great opportunity to illustrate the value of using Access as a database (over Excel) and also how to integrate its capabilities with the Word Mail Merge feature to streamline a process.

Here's her problem. Like you, she has many clients (in her case, freelance authors) to whom she needs to send contracts, but sometimes there is more than one assignment per author. This makes it harder to isolate the contract by using a Mail Merge to Excel because:

  • The author info is in a separate worksheet, or
  • There are lots of contracts for each author.

This is the classic one-to-many relationship that we covered in our early work with Access.

In our Surrealty Database, we illustrated how a realtor could have a table of clients, linked to a table of properties, because (again) each client could have one or more properties for sale or purchase.

We used this one-to-many relationship to create an Access Query that we could use to take advantage of these Relationships, and eventually we created a report based on the query.

It's entirely possible that you could continue down this path, and learn how to customize a report and write it in Access to do what we need to do – create a targeted Offer Sheet that we can save or print and send out quickly.

But I think it's a lot easier, in this instance, to use the mail merge capability of Word. The idea is that, frequently, we need to use the information in an Access database to generate a document with specific information, and we don't want to cut and paste a lot of stuff. We want it automated.

So, let's create a simple version of our Surrealty Offer sheet. This is a contract that a potential buyer or seller presents to purchase a property. As realtors, we need to prepare lots of these quickly.

(Going back to my editor example, with her authors or your clients, you will see how helpful it is to have data broken down into tables in Access.)

First, let's take another look at the Access database. We have one table for Listings, and another for Sellers. (Or actually clients — although the table is named "Sellers," some of these can also be buyers.)

The Relationships window shows a link between the Last Name field in Sellers and the Client field in Listings. This makes it possible to create a query that spans both tables.

So let's say the client wants to make an offer (to sell or buy) a specific property (in the Listings). We've entered all of the information in both tables, and now we know which property the offer is for.

The cool thing is that in the Listings, although there may be more than one property for each client, each property has a unique ID (its primary key). So if we know that the property for which the offer will be made is the one in Palms, we can see that its ID is 5.

We add that ID field to the query, and set its Criteria as 5.

When we run the query, we see all of the information for that property which we've entered, plus the client information. We could also add the client name and address and other stuff, but I kept it simple here.

We can close the query (which is named Westwood here) and click Yes to save it.

Now it's time to create our merge document. We can use our original Surrealty template as the basis for the document, and open the Mail Merge toolbar (View > Toolbars > Mail Merge).

Click the Open Data Source button (second from left) and locate the database with the query you want. Click Open.

Now, from the available data sources, select the saved and named query.

If you click the Insert Merge Fields button, and select Database fields, you see the available fields (limited, here, because we didn't fill out all of the tables).

Using this field selector, I begin to construct my document. I put in the fields for the areas of the text that I want merged.

I leave the offer price blank because I have flakey clients (I live in Los Angeles). But I could use an "OfferPrice" field in the client table just as easily for that information.

Now if I click the View Merged Data button, SHAZZAM! – the field notations are replaced by the one property I have selected in my query. Notice that my Next Record button won't move. If I had merged with the entire Query (without any criteria), I could cycle through all of the records with the same result.

The only thing left to do is click the Merge to New Document button.

In this case, I could use All or Current Record. I get the one I want as a complete new document, ready to print and save.

Let's take a look at one more scenario.

We'll go back and take out the criteria in the Query, and close and save it again. Now the entire set of Listings and Clients is going to be brought into the Word document. We need to close and reopen it again, or reimport the data source.

We can use the same technique to go through all of the merged listings and select the document(s) we want to merge to a new document and print. We can even use the Find Entry button (the one with the little binoculars) to bring up a field and item selector to do the same thing we did with the criteria in the original query.

In either case we used a query to combine the fields and records we want from more than one Access table, and used that information in a merge file document in Word. While we could surely automate some of this with a set of macros or VBA code, for a simple solution this should work quite well.