How to Apply a UNION in MS Access

In this short tutorial, I’ll show you the steps to apply a Union in MS Access between two tables. The same concepts can also be applied between two queries or more.

Why use UNION in MS Access?

You can use Union to add together the records from one table/query to the records from the other table/query (or more) for a specified set of fields.

The SQL structure to apply a Union in MS Access between two tables is as follows:

SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3
FROM Table1

UNION

SELECT
Table2.Field1,
Table2.Field2,
Table2.Field3
FROM Table2

Please note that the same number of fields needs to be used when performing the Union between the two tables or queries. In the above structure, we used exactly 3 fields from each table.

Also note that the data within the fields to be unionized should be of the same nature. For example, Field1 under both tables may represent the “Client First Name.”

Using the same field names and formats across the tables/queries will ensure a smooth parallelism when applying the Union.

Example

Let’s suppose that you want to perform a Union between the following two Access tables:

• Client_List_Feb_2018
• Client_List_Mar_2018

Now assume that each of the above tables contains the following 3 fields:

• Client_First_Name
• Client_Last_Name
• Client_Country_of_Residence

This is how the data in the first table (i.e., the Client_List_Feb_2018 table) would look like in Access:

And this is how the data in the second table (i.e., the Client_List_Mar_2018 table) would look like in Access:

Now, if you want to add together the records from the first table to the records from the second table, then you can apply the following SQL syntax in MS Access:

SELECT 
[Client_List_Feb_2018].Client_First_Name,
[Client_List_Feb_2018].Client_Last_Name,
[Client_List_Feb_2018].Client_Country_of_Residence 
FROM [Client_List_Feb_2018]

UNION

SELECT
[Client_List_Mar_2018].Client_First_Name,
[Client_List_Mar_2018].Client_Last_Name,
[Client_List_Mar_2018].Client_Country_of_Residence
FROM [Client_List_Mar_2018]

And the result of your query would look like this:

Apply a UNION across more than 2 tables

You can apply a Union in MS Access across more than just 2 tables by using the same concepts as described above.

Let’s suppose that now you have the following 3 tables:

• Client_List_Feb_2018
• Client_List_Mar_2018
• Client_List_Apr_2018

This is how the data would look like in the third table (i.e., the Client_List_Apr_2018 table):

Here is the SQL syntax that you’ll need to apply in order to add the records from the 3 tables together:

SELECT
[Client_List_Feb_2018].Client_First_Name,
[Client_List_Feb_2018].Client_Last_Name,
[Client_List_Feb_2018].Client_Country_of_Residence
FROM [Client_List_Feb_2018]

UNION

SELECT
[Client_List_Mar_2018].Client_First_Name,
[Client_List_Mar_2018].Client_Last_Name,
[Client_List_Mar_2018].Client_Country_of_Residence
FROM [Client_List_Mar_2018]

UNION

SELECT
[Client_List_Apr_2018].Client_First_Name,
[Client_List_Apr_2018].Client_Last_Name,
[Client_List_Apr_2018].Client_Country_of_Residence
FROM [Client_List_Apr_2018]

And here is the result:

Conclusion

As previously indicated, you’ll need to follow these general guidelines when applying a Union between tables or queries:

• The same number of fields needs to be used; and
• The data within the fields (in which you are going to add together) should be of the same nature; and
• The same field names and formats should be used across the tables/queries