Access Cookbook

Create a Query to Combine Data from Two Tables with Similar Structures

Problem

You have two tables of addresses, one for clients and one for leads. Generally you send different mailings to these two groups, but sometimes you need to send the same letter to both. You can always create a third table and append to it the data from each of the two tables, but there must be an easier way that doesn’t involve the use of temporary tables. Is there a way to combine the data from these two tables into a single recordset, including only the U.S. addresses and sorted by zip code?

Solution

Access provides a special type of query that you can use to vertically splice together the data from two or more tables. The tables don’t even need to have the same fields or fields of exactly the same data types. This is the union query, which can be constructed only by using the SQL View pane in the query designer.

The following steps show you how to construct a union query to combine data from two tables into a single recordset, limited to addresses in the U.S. and sorted by zip code:

1. Open 01-10.MDB. Open the two tables (tblClients and tblLeads) and examine their structure and data.

2. Create a new select query. Click on Close when you are prompted to add a table.

3. Select Query → SQL Specific → Union. Access will present a blank SQL view.

4. If you’d like, open tblClients in design view so you can see the field names while typing. Then type in the first part of the query:

   SELECT Company, Address1, Address2, ...