The information in this article applies to:
- Microsoft Access versions 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
The data from a union query appears to be sorted automatically according to
values in the first column selected even when the query does not have an
explicit ORDER BY clause. This happens because, by default, union queries
do not return duplicate records; they perform an implicit DISTINCT. To
determine distinct rows of data, a union query sorts the data.
Duplicate records are not returned unless UNION ALL is used. When the ALL
predicate is used, the union query's rows are returned unsorted unless an
ORDER BY clause in included in the last SELECT statement.
MORE INFORMATION
If you want to see the data from all the SELECT statements in the UNION
query including duplicates, keep the following tips in mind when you are
designing your queries:
- If any SELECT statement from the second through last does not have the
ALL predicate with its UNION clause, the records will be sorted by
default.
- Any SELECT statement that has the ALL predicate and is not followed by
another SELECT statement omitting the ALL predicate will have its rows
appended to the records returned by the preceding SELECT statement,
instead of sorted within the records of the preceding SELECT statement.
The following example demonstrates how to create a union query that returns
a sorted recordset, based on the first tip above:
- Start Microsoft Access and open the sample database Northwind.mdb (or
NWIND.MDB in version 2.0).
- Create a new query that is not based on any table.
- On the Query menu, click SQL Specific, and then click Union.
- Enter the following SQL statement in the Union Query window.
NOTE: The blank lines in the following sample SQL statement have no
effect on the query itself, but are meant to improve readability.
Similarly, the column for the constants Employees1, Employees2,
and Customers is included to identify each record's source. The
Customers SELECT statement is restricted to contact names
beginning with "K" in order to limit the number of records returned.
SELECT [FirstName] & " " & [LastName] as Fullname, "Employees1"
FROM Employees
UNION ALL SELECT [FirstName] & " " & [LastName] , "Employees2"
FROM Employees
UNION SELECT [ContactName], "Customers"
FROM Customers
WHERE [ContactName] LIKE "K*";
(NOTE: In Microsoft Access 2.0, [FirstName], [LastName], and
[ContactName] should all be typed as two words, as follows:
[First Name], [Last Name], [Contact Name].)
- On the Query menu, click Run.
NOTE: The rows from all three SELECT statements are sorted in a single
alphabetical sequence by Fullname. In this example, the SELECT
statement without an ALL predicate in the UNION clause happens to
be the last one. Notice also that duplicates are retained in these
query results.
The next example returns an unsorted recordset, based on the second tip
above:
- Reverse the second and third SELECT statements in the above example so
that the SQL statement looks like the following statement.
NOTE: The semicolon (;) should only appear at the end of the entire
statement.
SELECT [FirstName] & " " & [LastName] as Fullname, "Employees1"
FROM Employees
UNION SELECT [ContactName], "Customers"
FROM Customers
WHERE [ContactName] LIKE "K*"
UNION ALL SELECT [FirstName] & " " & [LastName] , "Employees2"
FROM Employees;
- On the Query menu, click Run.
NOTE: The order of the records is changed. The two Customers rows are
sorted within the records from the first SELECT (Employees1), and
the rows from the last SELECT (Employees2) are appended, in their
own sort order, to the rows of the first two SELECT statements.
REFERENCES
For more information about union queries, search the Help Index for "union
queries," or ask the Microsoft Access 97 Office Assistant.
|