Union query

A “Union query” combines the results of several similar “Select Queries”. For example, in our database we have a table of data about customers and another table that contains information about suppliers but, the two tables do not have a “Relationship”. 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 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 as “SQL query”. SQL is a code language 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 add the second query to the “Union 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”, will merge with the results of the “SELECT” statement that follows “UNION”.
• Save “ALL” means that duplicate rows are not removed when we merge both queries together.

When you have done this, you 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