“Union query”

A “Union Query” combines the results of several similar “Select Queries”.
For example, in our database we have a customers table and another suppliers table but, the two tables do not have a “Relationship”.
Then, we can create a “Select query” for each table but, this still gives us two different sets of data.

With a union query, we can merge the results of two or more “Select queries”.

For the “Union Query” to work, the different “Select Queries” should have the same number of fields in the same order and the same data type.

In this example I created a “Select query” from a customer list and a supplier list.

Both have the same number of fields in the same order and on the same data type.

With both queries open, click the “Query Design” button under the “Create” tab in the “Ribbon”.
In the “Show Table” dialog that appears, click the “Union” button in the “Ribbon”.

This hides the “Query Design” window and displays the “SQL view”.
A merge is shown in “SQL query”.
SQL is a code that tells “Access” what to do with the information.
Right-click the first tab of the query that you want to combine, in this case the “QueryCustomers” query.
Choose “SQL view” in the drop-down menu.
The SQL code for this query is displayed:

Select this code and copy it into the “Union Query”, in this case “query1”.
To create the “Union Query” with the second query, you need to type the semicolon (;) at the end of this first SQL code.

With the cursor at the end of the SQL code, click the “ENTER” key on your keyboard.
This brings it to a new line.
Type the word “UNION”, followed by the word “ALL”.
The word “ALL” is optional.
“UNION” means that the results of the “SELECT” statement that precede “UNION”, merge with the results of the “SELECT” statement that follows “UNION”.
“ALL” means that duplicate rows are not removed when we merge both queries together.
When you do this, we will have:

We do the same for “QuerySuppliers” query.
So click the “QuerySuppliers” tab, select the code and copy it to the “Union Query”.

Click the “Run” button in the “Ribbon”.
The results from both queries are now shown in the “Union Query”.

Save them if you wish to retain.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats