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

Sometimes you might want to combine records from one table or query with records from one or more other tables into a single result. That's what a union query does in Access.

To effectively understand union queries, you should first be familiar with designing basic select queries in Access. To learn more about designing select queries, see Create a simple select query.

Study a working union query example

If you've never created a union query before, it might help to first study a working example in the Northwind Access template. You can search for the Northwind sample template on the getting started page of Access by selecting File > New. You can also download a copy directly from Northwind sample template.

After Access opens the Northwind database, dismiss the login dialog box that first appears, and then expand the Navigation Pane. Select the top of the Navigation Pane, and then select Object Type to organize all database objects by type. Next, expand the Queries group, and you'll see a query called Product Transactions.

Union queries are easy to differentiate from other query objects because they have a special icon that resembles two intertwined circles representing a united set from two sets:

Unlike normal select and action queries, tables aren't related in a union query. That means you can't use the Access graphic query designer to build or edit union queries. If you open a union query from the Navigation Pane, Access opens it and displays the results in datasheet view. Under Views on the Home tab, notice that Design View isn't available when you work with union queries. You can switch only between Datasheet View and SQL View.

To continue your study of this union query example, click Home > Views > SQL View to view the SQL syntax that defines it. In this illustration, we've added some extra spacing in the SQL so you can easily see the various parts that make up a union query.

Let's look at the SQL syntax of this union query from the Northwind database in detail:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

The first and the third parts of this SQL statement are essentially two select queries. These queries retrieve two different sets of records; one from the Product Orders table and one from the Product Purchases table.

The second part of this SQL statement is the UNION keyword, which tells Access to combine these two sets of records.

The last part of this SQL statement determines the order of the combined records by using an ORDER BY statement. In this example, Access orders all records by the Order Date field in descending order.

Create a union query by creating and combining select queries

Even though you can create a union query by writing the SQL syntax directly in SQL View, you might find it easier to build it in parts with select queries. You can then copy and paste the SQL parts into a combined union query.

If you'd like to skip reading the steps and instead watch an example, see the next section, Watch an example of building a union query.

1. On the Create tab, in the Queries group, click Query Design.
2. Double-click the table that has the fields that you want to include. The table is added to the query design window.
3. 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.
4. Optionally, add criteria to your fields by typing the appropriate expressions in the Criteria row of the field grid.
5. 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.
6. Switch the query to Design view.
7. Save the select query, and leave it open.
8. Repeat this procedure for each of the select queries that you want to combine.

Now that you've created your select queries, it's time to combine them. In this step, you create the union query by copying and pasting the SQL statements.

1. On the Create tab, in the Queries group, click Query Design.
2. 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 tab is empty.
3. Click the tab for the first select query that you want to combine in the union query.
4. On the Home tab, click View > SQL View.
5. Copy the SQL statement for the select query. Click the tab for the union query that you started to create earlier.
6. Paste the SQL statement for the select query into the SQL View object tab of the union query.
7. Delete the semicolon (;) at the end of the select query SQL statement.
8. Press Enter to move the cursor down one line, and then type UNION on the new line.
9. Click the tab for the next select query that you want to combine in the union query.
10. 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.
11. On the Design tab, in the Results group, click Run.

The results of your union query appear in Datasheet view.

Watch an example of building a union query

Here's an example that you can recreate in the Northwind sample database. This union query collects the names of people from the Customers table and combines them with the names of people from the Suppliers table. If you'd like to follow along, work through these steps in your copy of the Northwind sample database.

Here are the steps necessary to build this example:

1. Create two select queries called Query1 and Query2 with the Customers and Suppliers tables respectively as data sources. Use First Name and Last Name fields as display values.

2. Create a new query called Query3 with no data source initially and then click the Union command on the Design tab to make this query into a Union query.

3. Copy and paste the SQL statements from Query1 and Query2 into Query3. Be sure to remove the extra semicolon and add the UNION keyword. You can then check your results in datasheet view.

4. Add an ordering clause to one of the queries, and then paste the ORDER BY statement into the union query in SQL View. Notice that in Query3, the union query, when the ordering is about to be appended, first the semicolons are removed, then the table name from the field names.

5. The final SQL that combines and sorts the names for this union query example is the following:

   SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
   FROM Customers
   
   UNION
   
   SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
   FROM Suppliers
   
   ORDER BY [Last Name], [First Name];
   

If you're very comfortable writing SQL syntax, you can write your own SQL statement for the union query directly in SQL View. However, you might find it useful to follow the approach of copying and pasting SQL from other query objects. Each query can be much more complicated than the simple select query examples used here. It can be to your advantage to create and test each query carefully before combining them in the union query. If the union query fails to run, you can adjust each query individually until it succeeds and then rebuild your union query with the corrected syntax.

Review the remaining sections of this article to learn more tips and tricks about using union queries.

Combine three or more tables or queries in a union query

In the example from the previous section that uses the Northwind database, data from only two tables is combined. However, you can combine three or more tables very easily in a union query. For example, building on the previous example, you might want to also include the names of the employees in the query output. You can accomplish that task by adding a third query and combining with the previous SQL statement with an additional UNION keyword like this:

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

When you view the result in datasheet view, all employees will be listed with the sample company name, which probably isn't very useful. If you want that field to show whether a person is an in-house employee, from a supplier, or from a customer, you can include a fixed value instead of the company name. Here's what the SQL looks like:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Here's how the result appears in datasheet view. Access displays these five example records:

Employment	Last Name	First Name
In-house	Freehafer	Nancy
In-house	Giussani	Laura
Supplier	Glasson	Stuart
Customer	Goldschmidt	Daniel
Customer	Gratacos Solsona	Antonio

You can reduce the query even further because Access reads the names of the output fields only from the first query in a union query. Here, the output from the second and third query sections is removed:

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Filtering in union queries

In an Access union query, ordering is allowed only once, but you can filter each query individually. Building on the previous section's union query, here's an example that filters each query by adding a WHERE clause.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Switch to datasheet view and you'll see results similar to this:

Employment	Last Name	First Name
Supplier	Andersen	Elizabeth A.
In-house	Freehafer	Nancy
Customer	Hasselberg	Jonas
In-house	Hellung-Larsen	Anne
Supplier	Hernandez-Echevarria	Amaya
Customer	Mortensen	Sven
Supplier	Sandberg	Mikael
Supplier	Sousa	Luis
In-house	Thorpe	Steven
Supplier	Weiler	Cornelia
In-house	Zare	Robert

Mixing data types

If the queries that you union are very different, you might encounter a situation in which an output field must combine data of different data types. If so, the union query most often will return the results as a text data type since that data type can hold both text and numbers.

To understand how this works, we'll use the Product Transactions union query in the Northwind sample database. Open that sample database and then open the Product Transactions query in datasheet view. The last ten records should be similar to this output:

Product ID	Order Date	Company Name	Transaction	Quantity
77	1/22/2006	Supplier B	Purchase	60
80	1/22/2006	Supplier D	Purchase	75
81	1/22/2006	Supplier A	Purchase	125
81	1/22/2006	Supplier A	Purchase	200
7	1/20/2006	Company D	Sale	10
51	1/20/2006	Company D	Sale	10
80	1/20/2006	Company D	Sale	10
34	1/15/2006	Company AA	Sale	100
80	1/15/2006	Company AA	Sale	30

Let's assume that you want to split the Quantity field into two fields: Buy and Sell. Let's also assume that you want a fixed zero value for the field with no value. Here's what the SQL looks like for this union query:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

If you switch to datasheet view, you'll see the last ten records now displayed like the following:

Product ID	Order Date	Company Name	Transaction	Buy	Sell
74	1/22/2006	Supplier B	Purchase	20	0
77	1/22/2006	Supplier B	Purchase	60	0
80	1/22/2006	Supplier D	Purchase	75	0
81	1/22/2006	Supplier A	Purchase	125	0
81	1/22/2006	Supplier A	Purchase	200	0
7	1/20/2006	Company D	Sale	0	10
51	1/20/2006	Company D	Sale	0	10
80	1/20/2006	Company D	Sale	0	10
34	1/15/2006	Company AA	Sale	0	100
80	1/15/2006	Company AA	Sale	0	30

Continuing this example, what if you want the fields with zero values to be empty? You can modify the SQL to display nothing instead of zero by adding the Null keyword, as shown here:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

However, as you might have observed switching to datasheet view, you now have an unexpected result. In the Buy column, every field is cleared:

Product ID	Order Date	Company Name	Transaction	Buy	Sell
74	1/22/2006	Supplier B	Purchase
77	1/22/2006	Supplier B	Purchase
80	1/22/2006	Supplier D	Purchase
81	1/22/2006	Supplier A	Purchase
81	1/22/2006	Supplier A	Purchase
7	1/20/2006	Company D	Sale		10
51	1/20/2006	Company D	Sale		10
80	1/20/2006	Company D	Sale		10
34	1/15/2006	Company AA	Sale		100
80	1/15/2006	Company AA	Sale		30

The reason this happens is because Access determines the data types of the fields from the first query. In this example, Null is not a number.

So what happens if you try to insert an empty string for the blank value of the fields? The SQL for this attempt might look like this:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

When you switch to datasheet view, you'll see that Access retrieves the Buy values, but it converted the values to text. You can tell these are text values since they are left-aligned in the datasheet view. The empty string in the first query is not a number which is why you see these results. You'll also notice that the Sell values are also converted to text because the purchase records contain an empty string.

Product ID	Order Date	Company Name	Transaction	Buy	Sell
74	1/22/2006	Supplier B	Purchase	20
77	1/22/2006	Supplier B	Purchase	60
80	1/22/2006	Supplier D	Purchase	75
81	1/22/2006	Supplier A	Purchase	125
81	1/22/2006	Supplier A	Purchase	200
7	1/20/2006	Company D	Sale		10
51	1/20/2006	Company D	Sale		10
80	1/20/2006	Company D	Sale		10
34	1/15/2006	Company AA	Sale		100
80	1/15/2006	Company AA	Sale		30

So how do you solve this puzzle?

One solution is to force the query to expect the field value to be a number. You can do that with this expression:

IIf(False, 0, Null)

The condition to check, False, is never True, so the expression always returns Null. However, Access still evaluates both output options and treats the output as numeric or Null.

Here's how we can use this expression in our working example:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

You don't need to modify the second query.

If you switch to datasheet view, you'll now see a result that we want:

Product ID	Order Date	Company Name	Transaction	Buy	Sell
74	1/22/2006	Supplier B	Purchase	20
77	1/22/2006	Supplier B	Purchase	60
80	1/22/2006	Supplier D	Purchase	75
81	1/22/2006	Supplier A	Purchase	125
81	1/22/2006	Supplier A	Purchase	200
7	1/20/2006	Company D	Sale		10
51	1/20/2006	Company D	Sale		10
80	1/20/2006	Company D	Sale		10
34	1/15/2006	Company AA	Sale		100
80	1/15/2006	Company AA	Sale		30

An alternative method to achieve the same result is to prepend the queries in the union query with yet another query:

SELECT
  0 As [Product ID], Date() As [Order Date],
  "" As [Company Name], "" As [Transaction],
  0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

For each field, Access returns fixed values of the data type you define. Of course, you don't want the output of this query to interfere with the results so the trick to avoid that is to include a WHERE clause to False:

WHERE False

This is a small trick. Because the condition is always false, the query doesn't return anything. Combining this statement with the existing SQL and we arrive at a completed statement as follows:

SELECT
  0 As [Product ID], Date() As [Order Date],
  "" As [Company Name], "" As [Transaction],
  0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Adding totals in a union query

A special use for a union query is to combine a set of records with one record that contains the sum of one or more fields.

Here is another example that you can create in the Northwind sample database to illustrate how to get a total in a union query.

1. Create a new simple query to view the purchase of beers (Product ID=34 in the Northwind database) using the following SQL syntax:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];
   

2. Switch to datasheet view and you should see four purchases:

   Date Received	Quantity
   1/22/2006	100
   1/22/2006	60
   4/4/2006	50
   4/5/2006	300

3. To obtain the total, create a simple aggregating query using the following SQL:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   

4. Switch to datasheet view and you should see only one record:

   MaxOfDate Received	SumOfQuantity
   4/5/2006	510

5. Combine these two queries into a union query to append the record with the total quantity to the purchase records:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];
   

6. Switch to datasheet view and you should see the four purchases with the sum of each followed by a record that totals the quantity:

   Date Received	Quantity
   1/22/2006	60
   1/22/2006	100
   4/4/2006	50
   4/5/2006	300
   4/5/2006	510

That covers the basics of adding totals into a union query. You might also want to include fixed values in both queries such as "Detail" and "Total" to visually separate the total record from the other records. You can review using fixed values in the section Combine three or more tables or queries in a union query.

Working with distinct records in union queries using UNION ALL

Union queries in Access by default only include distinct records. But what if you want to include all records? Another example might be useful here.

In the previous section, we showed you how to create a total in a union query. Modify that union query SQL to include Product ID = 48:

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Switch to datasheet view and you should see a somewhat misleading result:

Date Received	Quantity
1/22/2006	100
1/22/2006	200

Of course, one record doesn't return twice the total quantity.

You see this result because, on one day, the same quantity of chocolates was sold twice, as recorded in the Purchase Order Details table. Here's a simple select query result showing both records in the Northwind sample database:

Purchase Order ID	Product	Quantity
100	Northwind Traders Chocolate	100
92	Northwind Traders Chocolate	100

In the union query previously noted, you can see that the Purchase Order ID field isn't included and that the two fields don't make up two distinct records.

If you want to include all records, use UNION ALL instead of UNION in your SQL. This will most likely affect the sorting of the results, so you might also want to include an ORDER BY clause to determine a sort order. Here's the modified SQL based on the previous example:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Switch to datasheet view and you should see all the details in addition to a total as the last record:

Date Received	Total	Quantity
1/22/2006		100
1/22/2006		100
1/22/2006	Total	200

Use a union query to filter records on a form through a combo box control

A common usage for a union query is to serve as the record source for a combo box control on a form. You can use that combo box to select a value to filter the form's records. For example, filtering the employee records by their city.

To see how this might work, here's another example that you can create in the Northwind sample database to illustrate this scenario.

1. Create a simple select query by using this SQL syntax:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;
   

2. Switch to datasheet view and you should see the following results:

   City	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Looking at those results you might not see a lot of value. Expand the query, though, and turn it into a union query by using the following SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;
   

4. Switch to datasheet view and you should see the following results:

   City	Filter
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access performs a union of the nine records, previously shown, with fixed field values of <All> and "*". Because this union clause doesn't contain UNION ALL, Access returns only distinct records. That means each city is returned only once with fixed identical values.

5. Now that you have a completed union query displaying each city name only once, along with an option that effectively selects all cities, you can use this query as the record source for a combo box on a form. Using this specific example as a model, you could create a combo box control on a form, set this query as its record source, set the Column Width property of the Filter column to 0 (zero) to hide it visually, and then set the Bound Column property to 1 to indicate the index of the second column. In the Filter property of the form itself, you can then add code like the following to activate a form filter by using the value selected in the combo box control:

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True
   

   The user of the form can then filter the form records to a specific city name or select <All> to list all records for all cities.

Top of Page