Use a union query to combine multiple queries into a single result

Suppose your Access database has two unrelated tables – one that stores customer data and another that stores data about suppliers – and both tables have a contact information field. You'd like to see the contact information from both tables in one view. To do this, first create a select query for each table to retrieve the contact information, and then combine those results by creating a union query.

Note:  The content in this article is intended for use with Access desktop databases. You can't create or use a union query in Access web databases or Access web apps.

Need a refresher on creating basic select queries? See Create a select query.

First, a couple things to know about creating a union query:

• The select queries you combine in a union query need to have the same number of output fields, in the same order, and with the same or compatible data types. When you run a union query, data from each set of corresponding fields is combined into one output field so that the query output has the same number of fields as each of the SELECT statements.

• A union query is SQL-specific, and therefore must be written directly in SQL. You switch to SQL View to write SQL-specific queries, including union queries.

Step 1: Create the select queries

Note:  Although you can include multiple tables or queries in a query, this procedure assumes that each of your select queries includes data from only one table.

1. On the Create tab, in the Queries group, click Query Design.

2. In the Show Table dialog box, double-click the table that has the fields that you want to include. The table is added to the query design window.

3. Close the Show Table dialog box.

4. In the query design window, double-click each of the fields that you want to include.
   As you select fields, make sure that you add the same number of fields, in the same order, that you add to the other select queries. Pay careful attention to the data types of the fields, and make sure that they have compatible data types with fields in the same position in the other queries that you are combining. For example, if your first select query has five fields, the first of which contains date/time data, make sure that each of the other select queries that you are combining also has five fields, the first of which contains date/time data, and so on.

5. Optionally, add criteria to your fields by typing the appropriate expressions in the Criteria row of the field grid.

6. After you have finished adding fields and field criteria, you should run the select query and review its output. On the Design tab, in the Results group, click Run.

7. Switch the query to Design view.

8. Save the select query, and leave it open.

9. Repeat this procedure for each of the select queries that you want to combine.

For more information about using criteria in a query, see Apply criteria to a query.

Step 2: Combine the select queries

In this step, you create the union query by using SQL statements. (For more information, see the SQL syntax section, below.)

1. On the Create tab, in the Queries group, click Query Design.

2. Close the Show Table dialog box.

3. On the Design tab, in the Query group, click Union.

Access hides the query design window, and shows the SQL view object tab. At this point, the SQL view object tab is empty.

1. Click the tab for the first select query that you want to combine in the union query.

2. On the Home tab, click View > SQL View.

3. Copy the SQL statement for the select query. Click the tab for the union query that you started to create in step 1.

4. Paste the SQL statement for the select query into the SQL view object tab of the union query.

5. Delete the semicolon (;) at the end of the select query SQL statement.

6. Press Enter to move the cursor down one line, and then type UNION on the new line.

7. Click the tab for the next select query that you want to combine in the union query.

8. Repeat steps 5 through 10 until you have copied and pasted all of the SQL statements for the select queries into the SQL view window of the union query. Do not delete the semicolon or type anything following the SQL statement for the last select query.

9. On the Design tab, in the Results group, click Run.

The results of your union query appear in Datasheet view.

Top of Page

Tips for using union queries

• To be able to distinguish which rows came from which table, add a text string as a field in each of your select statements.

  For example, if one select statement is retrieving fields from a table named Products, and another select statement is retrieving fields from a table named Services, add the string "Product" as a field at the end of the first statement and "Service" at the end of the second statement. You can also assign a field alias (for example, "type") to the strings by using the AS keyword, as shown in the following example:

  SELECT field1, field2, ... "Product" AS type

  SELECT field1, field2, ... "Service" AS type

  The query output would include a field named "type" that displays whether the row came from the Products table or the Services table.

• Each UNION keyword combines the SELECT statements that immediately precede and follow it. If you use the ALL keyword with some of the UNION keywords in your query, but not with others, the results will include duplicate rows from the pairs of SELECT statements that are combined by using UNION ALL, but will not include duplicate rows from the SELECT statements that are combined by using UNION without the ALL keyword.

• The number, data types, and order of the fields in the select queries must correspond. You can use expressions, such as calculations or subqueries, to make them correspond.

  For example, you can match a two-character year field with a four-character year field by using the Right function to extract the last two digits of the four-character year.

Using the results of a union query to create a new table

First, use the union query as input for a new select query, and then use that select query as the basis of a make-table query, as shown in the following steps:

1. Create and save the union query.

2. On the Create tab, in the Queries group, click Query Design.

3. In the Show Table dialog box, click the Queries tab.

4. Double-click your union query, and then close the Show Table dialog box.

   Note: If you see a Security warning in the Message Bar, action queries might be disabled. To enable action queries, click Enable Content on the Message Bar.

5. In the query design grid, on your union query's object tab, double-click the asterisk (*) to use all the union query's fields to make a new table.

6. On the Design tab, in the Query Type group, click Make Table.

7. In the Make Table dialog box, type a name for the new table.

8. Optionally, specify a different database in which to create the table.

9. Click OK.

10. On the Design tab, in the Results group, click Run.

Using a union query to perform a full outer join

You can use a union query to perform a full outer join. A full outer join does not limit the rows that are returned from either of the joined tables, but does combine rows on the basis of values in the join field.

1. Create a query that has a left outer join on the field that you want use for a full outer join.

2. On the Home tab, in the Views group, click View, and then click SQL View.

3. Press Ctrl+C to copy the SQL code.

4. Delete the semicolon at the end of the FROM clause, and then press Enter.

5. Type UNION, and then press Enter.

   Note: Do not use the ALL keyword when you use a union query to perform a full outer join.

6. Press Ctrl+V to paste the SQL code that you copied in step 3.

7. In the code that you pasted, change LEFT JOIN to RIGHT JOIN.

8. Delete the semicolon at the end of the second FROM clause, and then press Enter.

9. Add a WHERE clause that specifies that the value of the join field is NULL in the first table listed in the FROM clause (the left table).

   For example, if the FROM clause is:

   FROM Products RIGHT JOIN [Order Details] 
   ON Products.ID = [Order Details].[Product ID]
   

   You'd add the following WHERE clause:

   WHERE Products.ID IS NULL

10. Type a semicolon (;) at the end of the WHERE clause to indicate the end of the union query.

11. On the Design tab, in the Results group, click Run.
    The results of your union query appear in Datasheet view.

For more information, see Join data sources in a query.

Top of Page

Requirements and SQL syntax for a union query

Some requirements for a union query

• The select queries that you combine in a union query need to have the same number of output fields, in the same order, and with the same or compatible data types. When you run a union query, data from each set of corresponding fields is combined into one output field so that the query output has the same number of fields as each of the SELECT statements.

  Note: For the purposes of a union query, the Number and Text data types are compatible.

• A union query is SQL-specific, and therefore must be written directly in SQL. You switch to SQL View to write SQL-specific queries, including union queries.

SQL syntax of a union query

In a union query, each select query (also called a SELECT statement) has the following clauses:

Clause	Contains a list of…
SELECT	fields containing the data that you want to retrieve.
FROM	tables containing those fields.
Optional WHERE	criteria for those fields.

The SELECT statements are combined by using the UNION keyword.

The basic SQL syntax for a union query is as follows:

SELECT field_1[, field_2,…]
FROM table_1[, table_2,…]
UNION [ALL]
SELECT field_a[, field_b,...]
FROM table_a[, table_b,…];

For example, if your database has a table named Products and another table named Services and both tables have fields that contain the name of the product or service, the price, warranty or guarantee availability, and whether you offer the product or service exclusively. Although the Products table stores warranty information, and the Services table stores guarantee information, the basic information is the same (whether a particular product or service comes with a promise of quality). You can use a union query, such as the following one, to combine the four fields from the two tables:

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services;

Let's examine the preceding syntax example, line by line:

Syntax	Explanation	Additional information
SELECT name, price, warranty_available, exclusive_offer	First SELECT clause	SELECT is followed by a list of identifiers that indicate the fields from which you want to retrieve data. A SELECT clause must list at least one field. This SELECT clause lists the field identifiers name, price, warranty_available, and exclusive_offer.
FROM Products	First FROM clause	A FROM clause follows a SELECT clause, and together they form a basic SELECT statement. FROM is followed by a list of identifiers that indicate which tables contain the fields listed in the SELECT clause. A FROM clause must list at least one table. This FROM clause lists the table identifier Products.
UNION ALL	The UNION keyword and the optional ALL keyword	The results of the SELECT statement that precedes UNION are combined with the results of the SELECT statement that follows UNION. When you use the ALL keyword, duplicate rows are not removed from the union query results. By using the ALL keyword, the query can run faster because Access doesn't have to check for duplicate rows. Use the ALL keyword if any of the following conditions are true: You're certain that the select queries will not produce any duplicate rows. It doesn't matter to you if your results have duplicate rows. You want to see duplicate rows.
SELECT name, price, guarantee_available, exclusive_offer	Second SELECT clause	Some rules: The second SELECT clause needs to have the same number of fields as the first SELECT clause. Fields that share common data need to appear in the same order in the clause. Fields that share common data need to have the same or compatible data types. Note: The names of the fields in the output of a union query are from the first SELECT clause. So, in our example, data from the field "warranty_available" and from the field "guarantee_available" are named "warranty_available" in the query output.
FROM Services	Second FROM clause	There are no restrictions on the tables in the FROM clauses of a union query. You can create a union query that uses the same tables in each FROM clause. You can have different numbers of tables in the FROM clauses. In our example, the FROM clauses each have only one table.

Top of Page