Combine data in fields from two or more tables using a union query

Union queries combine corresponding fields from two or more tables or queries into one field. When you run a union query, it returns the records from corresponding fields in the included tables or queries.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
   
   
2. In the New Query dialog box, click Design View, and then click OK.
   
   
3. Without adding tables or queries, click Close in the Show Table dialog box.
   
   
4. On the Query menu, point to SQL Specific, and then click Union.
   
   
5. Enter SQL SELECT statements combined with either the UNION operation if you don't want to return duplicate records or the UNION ALL operation if you do want to return duplicate records.

   Note   Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.

   

   For information on SELECT statements, click For information on the UNION and UNION ALL operations, click .

6. If you want to specify sorting in a union query, add a single ORDER BY clause to the end of the last SELECT statement. In the ORDER BY clause, specify the field name to sort, which must come from the first SELECT statement. For information on the ORDER BY clause, click .
   
   
7. To see the query's results, click View on the toolbar.

Caution   If you convert a union query to another type of query, such as a select query, you'll lose the SQL statement that you just entered.

Notes

• The query Customers and Suppliers by City in the Northwind sample database is an example of a union query. To view this query, open the Northwind database in your Office folder's Samples subfolder, and then open the Customers and Suppliers by City query in Design view. For information on opening Northwind, click .
  
  
• A union query takes its column names from the column names in the first table or SELECT statement. If you want to rename a field in the results, use the AS clause to create an alias for the fields. For an example, click .