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 (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.
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];
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.