Access Union Queries Made Simple

Imagine this: you have three tables that contain contact information such as addresses and phone numbers. One table has prospects, one has customers, and one has vendors. You’d like to combine the contact lists into one, but the tables aren’t related. This isn’t something that can be done easily unless you know about the union query.

According to Microsoft, “A union query combines the result sets of several similar select queries.” The beauty of a union query is that you do not need to have relationships between the tables in order to use it.

In this article, you’ll learn how to create union queries easily and simplify the process with the clipboard.

Before you begin, here are the rules.

Union Query Rule #1

Union queries must be written directly in SQL; they cannot be created using the design grid. If you are not familiar with SQL, no worries. I will show you how to create union queries without having to know SQL. All you have to know is how to type one word, UNION.

Union Query Rule #2

Union queries must have the same number of output fields from each table. You cannot use a union query, for example, if you are pulling the last name, first name and phone number from Table1 and the last name, first name, phone number and address from Table2.

Union Query Rule #3

Output fields must be in the same order. If the first field you pull from the Table1 is last name, the first field from all subsequent tables must also be the last name field.

Union Query Rule #4

Output fields must be compatible data types. Access doesn’t care about the field names; it only checks for data types. If the first field from Table1 is a Date field, for example, the first field from Table2 cannot be text.  The first field  from Table1 can be named “Date” and the first field from Table2 can be named “DOB.” This is acceptable to Access as long as both fields have the same data type.

Creating an Access Union Query – Step-by-Step

The simplest way to create a union query without knowing SQL is to create separate queries to query each table in the query design, and copy and paste the SQL statements into one window.

Step 1 – Open the Clipboard.

Open the Clipboard. Click the Home tab on the ribbon, and click the dialog box launcher in the Clipboard group.

The Clipboard will launch on the left-hand side of the screen. If you have anything in the clipboard, click the Clear All button.

Step 2 – Create the First Query

You will create the first query to hold your union query.

Create a new query. Click the Create tab, then click Query Design.

When the Show Table box pops up, click the Close button.

Switch to SQL view. Click on the Design tab. In the Results group, click the arrow under View and and select SQL.

Step 3 – Create A Query for the First Table

Click the Create tab, then Query Design.

When the Show Table dialog box pops up, double-click the name of the table and then click the Close button.

Add the fields to your query design, remembering the rules.

Switch to SQL view, and copy the code to the clipboard. Select the code by typing CTRL A. Copy the code by typing CTRL C.

Step 4 – Create Queries for the Additional Tables

Go through the steps of creating queries for additional table(s) and copying the SQL statement(s) to your clipboard.

Step 5 – Use the Clipboard to Combine Code

Create a brand new query in SQL view.

Make sure the Clipboard is visible.

Paste one query’s SQL statements into the blank SQL window by clicking the arrow next to the first query’s SQL statement in the Clipboard window.

Make sure your cursor is in SQL view. Hit the Backspace key to remove the semi-colon. Click the Enter key so that you are on a new line. Type the word UNION and hit Enter again.

Paste the second query’s statement into the SQL window. If this is your last query, keep the semi-colon and run the query to view your results. If this isn’t your last query, delete the semi-colon, add the word UNION and paste the next query.

After pasting your final query’s SQL statement, make sure you end it with the semi-colon.

Run the query to view your results.