How Can I Order Data from Multiple Tables Using UNION in MS Access?

Using UNION to Combine and Order Data from Multiple MS Access Tables

In larger databases, combining data from multiple tables is a common task. The UNION operator provides a way to merge rows from different tables into a single result set. However, achieving the correct ordering of this combined data requires a specific approach in MS Access.

A straightforward attempt like this will fail:

<code class="language-sql">SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1</code>

The MS Access Jet database engine doesn't permit ORDER BY clauses directly within a UNION query.

The Solution: Nested Queries and Aliases

The solution involves nesting each individual SELECT statement within its own subquery and then applying the ORDER BY clause within each subquery. This requires assigning an alias to each subquery:

<code class="language-sql">SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) AS DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) AS DUMMY_ALIAS2</code>

This method allows for proper ordering of the combined data from table1 and table2. Note the use of UNION ALL which includes all rows from both queries, including duplicates, unlike UNION which removes duplicates. Choose the appropriate UNION operator based on your needs. This technique effectively enables sorting of data combined using the UNION operator in MS Access.

The above is the detailed content of How Can I Order Data from Multiple Tables Using UNION in MS Access?. For more information, please follow other related articles on the PHP Chinese website!