Union Queries

This describes how to 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.

 

If using Access 2003, Access XP, Access 2002, Access 2000, Access 97:

 

Creating a Union Query

1. In the Database window, click the Queries tab, and then click New.

2. In the New Query dialogue box, click Design View, and then click OK.

3. Add the first table you want to use in the Union query to the Query window and select the relevant fields from it.

4. Go to the SQL view of the query you have created and highlight and copy the SQL statement – it is important that you do so.

5. Change the Query type to Union – to do this click on the Query menu, choose SQL Specific and then Union from the choices given. The SQL window of the query will change, in the title bar you will see that it is now defined as a Union Query, and the code will have disappeared.

6. Paste the copied code into the Union Query.

7. At the end of the SQL statement press enter and they type UNION ALL. At this stage save the query with an appropriate name, eg qStockBalanceUnion, keep the query open.

8. Create a new query (keeping the half finished union query open). In the new query window select the next table you require to create the union query and pull down the relevant fields.

9. Go to the SQL design of the new query and copy the SQL statement.

10. Go to the half finished Union Query and paste the SQL statement into it after the words UNION ALL.

11. Save the changes to the Union Query. Close the second query you created and do not save it (this query was only created in order to copy the SELECT statement from it),

12. To see the results of the Union Query, click the run button on the Query 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.

Note:  Each SELECT statement in the Union Query 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.

 

Sorting Data in a Union Query

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.

 

Renaming Fields in Union Queries

A union query takes its field names from the field 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 example if you had a field called Balance which you were splitting into two fields called Budgets and Actuals, you would rename them as follows:

Balance AS Budgets, Balance AS Actuals

 

Grouping and Summing the Results of a Union Query

To group and sum the results of the union query:

1. Create a new Select Query

2. Add the Union Query to the Select Query design window

3. Select the fields from the Union Query in the design grid.

4. Click on the Totals icon on the query toolbar to group the fields (a new row for "Groups" sill be created in the design of the query)

5. Click on the drop down arrow to the right of the Group row in fields to be summed/averaged etc and select from the choices.

6. Click on the "Run" icon to see the results of the Select Query.

7. Save the query with a similar name to the Union Query it takes its information from, eg qCOMPANYsel.

 

Applies to:  Microsoft Access 2003, Access XP, Access 2002, Access 2000, Access 97