Access Cookbook - Ken Getz [26]
Country
FROM tblClients
WHERE Country = "U.S.A."
Yes, you must type it—there 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.
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."
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
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
Save the new query with a name of your choice; in the sample database, it is called qryBothLists.
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—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.
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.
TIP
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.
1.12. Create a Combo Box That Allows a User to Select N/A
Problem
Solution
You'd like to be able to create a combo box that looks up items in a table and is limited to this list of items, but with the additional choice of
You can set the LimitToList