لطفا منتظر باشید ...
Recipe 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:
Open
01-11.MDB . Open the two tables (tblClients
and tblLeads) and examine their structure and data.
Create a new select query. Click on Close when you are prompted to
add a table.
Select Query
SQL Specific Union. Access will
present a blank SQL view.
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 itthere 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.
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 fieldsnot their namesto determine which
fields'' data to combine together.
If a matching field is absent from
one of the tablesas is the case for tblLeads, which lacks an
Address3 fieldyou 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.
|
