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:  MS Access IsNull Function with Examples in SQL Query and VBA Code

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.

Related Access Database

  • ms access union query
  • how to create quotation form using access
  • microsoft access union all
  • ms access union select number format
  • payroll access database


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

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to


Must read×

Top