|[ Team LiB ]|
Recipe 1.11 Create a Query to Combine Data from Two Tables with Similar Structures
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?
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:
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—not their names—to determine which fields' data to combine together.
If a matching field is absent from one of the tables—as is the case for tblLeads, which lacks an Address3 field—you 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.
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.
|[ Team LiB ]|