Table of Contents
- Surrealty: An Organic Case Study
- Working with Microsoft Word
- Accelerating Your Knowledge of Excel
- Maintaining a Positive Outlook
- "Where Are My Socks?" Accessing Your Important Information
- Presenting Professionally with PowerPoint
- Posting a Web Site with FrontPage
- Publish or Perish
- Get Visual with Visio
Tools That Integrate Your Office Applications
- Creating Video E-Mail with MovieMaker
- Managing Pictures with Microsoft Office Picture Manager
- New Year's Predictions: 2005
- Office Predictions for 2006
- Favorite Books List
- Using Excel as a Database Conversion Tool for Outlook
- Oh, Brother, I Love Labels (and other Office Tips)
- Planning for Disaster
- Using OneNote with Outlook
- Web Resources for Microsoft Office
- Simple 3D in Microsoft Office
- Creating Dynamic Database Links
- Using an Access Query for Mail Merge
- Displaying Database Links with Xcelsius Enterprise
- An Office 12 Sneak Preview from PDC
- My Big Fat Office Vacation
- What CES 2006 Means to Office Users
- Using "Send To" Between Office Applications: Word and
- Running (and Surviving) a Web-based Conference
- Running an Online Office with HyperOffice and Writely
- Preparing with Index Cards
- Creating Meeting Agendas
- Collecting Data with New Technologies: ARS, SMS and RFID
- Using Application Sharing in a Web Conference
- Running an Online Notes or Windows Media Session
- Trying Out Live Meeting
- Creating a SharePoint Team Website
- Using and Customizing a SharePoint Team Website
- Creating a Trip Planner in Excel and Outlook
- Crystal Graphics’ Excel and Solutions and Chart
- GoToMeeting Instant Webinar Tool
- Checking Out Office Live
- Using Quindi Meeting Capture
- Using Excel to Link to Other Databases
- Trying Out Mind Manager Pro to Brainstorm with Office Programs
- The 13th Thing I Hate About Office
- Introduction to Office 2007
- What's New in Excel and PowerPoint 2007
- Take a Look at InfoPath 2007
- Office's Groovy New Collaboration Program
- Using Office Accounting Express
- Printing to PDF or XPS in Office 2007
- Getting Adjusted to Office 2007 Changes
- Using SnagIt for IT Training
- Providing Help with Go To My PC
- Vista Meeting Space and People Near Me from Microsoft
- Trying Expression Web
- Migration Issues to Word and Outlook 2007
- Vista – Are You Kidding Me?
- Making Office 2007 (and Vista) Work Properly
- Office and the Enterprise
- Survey Says – Use Web Surveys with Excel and Access
- Uninstalling Office 2007 in Windows XP Pro
- Using Excel for Tables in Office 2007
- VIDITalk – Video in SharePoint and Beyond
- Career Advancement for Office Professionals
- Online Database that Rivals Access?
- Web 2.0 2008 in San Francisco
- Going Virtual for MS Office
- Going Virtual Using Mobile Apps
- Managing Your Contacts Across the Office Suite
- Charts in PowerPoint and Excel 2007 (Video Update)
- Outline View: The Document Planning Bridge between Word and PowerPoint
- Using Document Inspector in Office 2007
- SmartDraw: A Powerful Communications Tool to Supplement MS Office
- Visio 2007's New Pivot Diagram
- Using the Macro Recorder in Visio 2007 (Video Update)
- Compatibility Pack: Challenges of Using Office 2007 Documents in Previous Versions
- Microsoft Office Live Small Business Beta
- No One Asked Me But... What I Want (and Don’t Want) in the Next Office and Windows
- Late New Year's Resolution: Keys to Effective IT Communication
- SmartDraw Extras: Healthcare and Legal Templates
- Interesting Upgrades: Camtasia 6 and SnagIt 9
- Addressing the Office 2007 Read-Only Runaround
- Getting Organized with OneNote
- Video Tutorials
- Additional Resources
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.
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.