You are currently offline, waiting for your internet to reconnect

ACC: Sort Order of Union Queries Affected by ALL Predicate

This article was previously published under Q117164
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
The data from a union query appears to be sorted automatically according tovalues in the first column selected even when the query does not have anexplicit ORDER BY clause. This happens because, by default, union queriesdo not return duplicate records; they perform an implicit DISTINCT. Todetermine distinct rows of data, a union query sorts the data.

Duplicate records are not returned unless UNION ALL is used. When the ALLpredicate is used, the union query's rows are returned unsorted unless anORDER BY clause in included in the last SELECT statement.
If you want to see the data from all the SELECT statements in the UNIONquery including duplicates, keep the following tips in mind when you aredesigning 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 returnsa sorted recordset, based on the first tip above:
  1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
  2. Create a new query that is not based on any table.
  3. On the Query menu, click SQL Specific, and then click Union.
  4. 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].)
  5. 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 tipabove:
  1. 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;
  2. 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.
For more information about union queries, search the Help Index for "unionqueries," or ask the Microsoft Access 97 Office Assistant.

Article ID: 117164 - Last Review: 12/04/2015 10:17:10 - Revision: 2.1

Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbinfo kbusage KB117164