Creating a UNION Query in Microsoft Access - Combine multiple datasets into one view
I'm going to demonstrate how to create a UNION query in Microsoft Access, which will allow you to combine the data from multiple tables all into one view. This will allow you to do things like run counts on data from different tables, all in one place. In the example I provided, I had employee data from three different datasets that represent cities. By using a UNION query, I can make queries and reports with information from all datasets.
0:08 - Brief Explanation
0:36 - First example (combine 2 tables)
2:13 - Second example (combine 3 tables)
3:07 - A tip to distinguish each original table/data source in the UNION query
Finally, here is the SQL code I used in the MS Access Design Builder:
SELECT chicagoteam.employee, chicagoteam.position, chicagoteam.employeeid, "Chicago" as Location
FROM chicagoteam
UNION
SELECT phoenixteam.employee, phoenixteam.position, phoenixteam.employeeid, "Phoenix" as Location
FROM phoenixteam
UNION SELECT houstonteam.employee, houstonteam.position, houstonteam.employeeid, "Houston" as Location
FROM houstonteam
ORDER BY [employee];
