Examples of union queries (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

ShowSorting records

The following union query (union query: A query that uses the UNION operator to combine the results of two or more select queries.) selects all company names and cities from both the Suppliers and Customers tables, and sorts the data alphabetically by city.

SELECT [CompanyName], [City]
FROM [Suppliers]

UNION SELECT [CompanyName], [City]
FROM [Customers]
ORDER BY [City];
            

ShowRenaming fields

The following union query (union query: A query that uses the UNION operator to combine the results of two or more select queries.) renames the CompanyName field to "Supplier/Customer Name" in the query output.

SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Suppliers]

UNION SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Customers];
            

ShowReturning duplicate records

The following union query (union query: A query that uses the UNION operator to combine the results of two or more select queries.) uses the UNION ALL statement to retrieve all records, including duplicates.

SELECT [CompanyName], [City]
FROM [Suppliers]

UNION ALL SELECT [CompanyName], [City]
FROM [Customers];
            
 
 
Applies to:
Access 2003