Ms Access Union Query SQL Function Syntax

Ms Access Union Query is the SQL function to combine multiple tables or queries into a single result, its not the same as the Select function, because Select take the fields from selected tables (or queries) into new columns, while union will combine the same fields without adding new fields as column.

The Syntax for Union Query Function is:
[SQL Statement A]
UNION [ALL]
[SQL Statement B];


[ALL]: the optional statement to combine all of the field. If you use [ALL], the duplicate value in both database will be added. If it omitted, it will only add the distinct value.

Union Query Example:

Ms Access Union Query (1)

Ms Access Union Query (1)

 

Ms Access Union Query-2

Ms Access Union Query (2)

 

From the pictures above, there are 2 database tables for Supplier and Customer contacts. The client wants to see all of the contacts. Now, create a new query and go to SQL view. Input this code:
SELECT Name, Address, Phone FROM [Customer Contacts]
UNION
SELECT Name, Address, Phone FROM [Supplier Contacts];
If you done it right, it will be turned into example B in the first picture.

Ms Access Union Query (3)

Ms Access Union Query (3)

 

Because the Union function is not using [ALL] parameter, the duplicate records named Daniella Shop only presented as one single record in the result. With [ALL] parameter, it should be:
SELECT Name, Address, Phone FROM [Customer Contacts]
UNION ALL
SELECT Name, Address, Phone FROM [Supplier Contacts];

Read Also:  Wedding Planner Template and Checklist for Microsoft Access

Ms Access Union Query-4

Ms Access Union Query (4)

 

Note in using the UNION function without [ALL] parameters: If one of the fields in the duplicate records is differ or changed, it will be counted as different records. Example: in Daniella Shop, only the Address field is changed. In the result, there will be 2 records for Daniella Shop with same Phone Number, but different addresses.

The UNION Query function can combine more than 2 tables or queries. To add more than 2 tables or queries, see this syntax:
[SQL Statement A]
UNION [ALL]
[SQL Statement B]
UNION [ALL]
[SQL Statement C]
UNION [ALL]
[SQL Statement Unlimited];

The UNION function only can be used if the selected fields from the tables are have same name. You need to change the different names. The function will combine the field regardless the different Data Type from the fields in different tables.



Access Database Tags: #access combine two tables with same fields #access union all query #access union query make table #combine tables in access #how to combine two sql queries in one result #pass through query access #sql combine two queries with different columns #union query access 2013 #union query access 2016

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top