Microsoft Access Queries – Union Queries

Union Queries 

Select queries allow you to bring together information from multiple tables and queries and add information into the same records but they do not allow you to combine two individual queries together.

For instance if you wanted to produce a christmas card list for your customers and suppliers from the customers and suppliers table then the select query facilities will give you two different lists.  If those lists need to be combined then a Union query needs to be set up.

If you know SQL then the query can be written directly by changing the view of the query to SQL View.  Otherwise, the two queries can be created individually and then combined.

The customer query can be created.



The supplier query can be created



When the second query is being set up, it is very important that the fields are in an identical order and named the same as the fields in the first query.

 If the query is to be sorted in any way then the sorting has to be done in the second query only.
Once the two queries have been set up then their SQL code can be copied over into a third query.

Open the two queries in SQL view 

1. Create a new query, change the view in SQL

2. Copy the SQL statement from the first created query

3. Paste it into the new query

4. Remove the last semi colon

5. Type in the word union

6. Copy the SQL statement from the second created query

7. Paste it into the new query after the word union

8. (If a sort order was selected then remove any reference to the table in the sort order.)







The union query cannot be then viewed as a select query.

The first two queries are no longer needed and can be deleted, but should you need to fine tune the union query then it will need to be done in SQL.

(Otherwise, by keeping the original two queries, then the changes can be done to the originals and combined again!)