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

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

1.11.1 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?

1.11.2 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, Address3, City, StateProvince, ZipPostalCode,
   Country
   FROM tblClients
   WHERE Country = "U.S.A."

   
   

   Yes, you must type it梩here is no query by example
   equivalent to a union query. However, you could create this select query first
   using the query grid and then copy and paste the SQL into your new union
   query.



5. 
   

   Type UNION, and then enter the matching fields
   from tblClients in the same order in which they were entered in Step 4:

   
   

   UNION SELECT LeadName, Address1, Address2, "", City, State, Zip, Country
   FROM tblLeads
   WHERE Country = "U.S.A."

   
   


6. 
   

   To sort the query's output by zip code, add an ORDER
   BY statement using the name of the field as it appears in the first
   table:

   
   

   ORDER BY ZipPostalCode;

   
   

   The completed query is shown in Figure 1-30.

   
   
   

   Figure 1-30. The completed union query

   
   

   
   

   
   


7. 
   

   Switch to datasheet view to see the output of the query, as
   shown in Figure 1-31. Notice that the Canadian addresses are excluded and that
   all the addresses are sorted by zip code.

   
   
   

   Figure 1-31. Output of the union query

   
   

   
   

   
   


8. 
   

   Save the new query with a name of your choice; in the
   sample database, it is called qryBothLists.

1.11.3 Discussion

The SQL UNION statement joins together the output of
two or more SELECT statements into a single result set. The field names
from the tables need not match, but they must be entered in the same order. If
matching fields in the tables appear in different positions but have the same
name, you must reorder them in the SELECT statements because Access
uses the order of the fields梟ot their names梩o determine which fields' data to
combine together.

If a matching field is absent from one of the tables梐s is
the case for tblLeads, which lacks an Address3 field梱ou can include a constant.
In the qryCombinedLists example, we used a zero-length string constant (""), but
we could have used another constant, such as None or N/A.

You can also add a column called Type that contains either
"Client" or "Lead", depending on which table it comes from, as shown in
qryCombinedListswType in the sample database. Here's the SQL for that query:

SELECT Company, Address1, Address2, Address3, City, StateProvince, ZipPostalCode,
Country, "Client" AS Type
FROM tblClients
WHERE Country = "U.S.A."

UNION SELECT LeadName, Address1, Address2, "", City, State, Zip, Country,
"Lead" AS Type
FROM tblLeads
WHERE Country = "U.S.A."
ORDER BY ZipPostalCode;

While typing in the text of the union query, you may find it
helpful to keep the source tables open in design view so you can be sure you are
entering the field names correctly. Or you can just "cheat" and use the query
designer to create SELECT statements that you copy and paste into your
union query.

Some dialects of SQL require the SQL statement to end with a
semicolon. Access does not, but it doesn't hurt to use the standard syntax,
especially if you program in other databases too.

A union query is a snapshot of the data in the underlying
tables, so it can't be updated.

To sort a union query, add one ORDER BY
clause at the end of the last SELECT statement, referring to the sort
fields using the field names from the first SELECT clause (as in the
sample query). You can't sort each SELECT clause individually; you have
to sort the whole union query. Any criteria should be included in WHERE
clauses in the respective SELECT statements. You can't use one
WHERE clause at the end of a union query to filter all the records.

 

A union query automatically screens out duplicate records (if any); if you want to include duplicates in the query's result set, use UNION ALL in place of the word UNION. This can also improve performance, since Access can skip the extra work of checking for duplicates.