Union Query in Access SQL

 

Union Query Example

Any/All as a Choice in a Combo Box

Union query example shows how to create a combo box All  or Add New choice item. This technique is often used for selecting specific (or All) records for a report.The SQL union query is used in this example is perhaps the simplest case for a union query.  This type of query is probably one of the most powerful and useful features in the SQL language.  We assume you have read our Microsoft Access database design recommendations so that the naming conventions we use make sense to you.

Union Query in Access

Note that we are using M_Employees in the second Select statement of the union query.  This could actually be any table name but the SQL syntax requires us to have a From clause.  In Oracle the common method is to use a table called DUAL. New! Download Access example of the Union Query

More common union queries select real data from two or more real tables.  Sometimes the tables in both select statements will be the same and in some circumstances the tables will be different. The only requirement is the you use the same number of fields in each select statement.  You can also have numerous union statements within a single query.

Note: Putting a blank letter before the ‘A’ in All makes this combo choice All choice sort to the top.Warning:  General warning about union queries –  Never try to use aggregate functions (avg, count, sum, etc.) in a union query.  It will drop out duplicate groups of records (in Access at least ).

Here is a union query example in which we want to find the total quantity of mittens sold.  In this case we have two tables:  Orders and Closed_Orders – we are not sure in which table the order will be so we query both tables at the same time using the union query:

SELECT Orders.Product, Sum(Orders.Qty) AS SumOfQty
FROM Orders
WHERE (((Orders.Product)=”Mittens”))
GROUP BY Orders.Product
Union
SELECT Product, Sum(Qty) AS SumOfQty
FROM Closed_Orders
WHERE ((Product)=”Mittens”)
GROUP BY Product

Below are the results of the union query:

  Product SumOfQty Mittens 2

Another Union Query Examples

Here is a real life example of an union query in our Preventative Maintenance (PM) access template:

SELECT 0 AS Due_Date, L_Equip_Makes.Equip_Make, T_Equip_PM_Readings.Equip_PM_Def_ID, L_Locations.Location, L_Equip.Equip_Descr, L_PM_Types.PM_Type, T_Equip_PM_Readings.Meter_Value, T_Equip_PM_Readings.PM_Meter_Value, Val(meter_value)-Val(pm_meter_value) AS run_hours, T_Equip_PM_Readings.PM_Interval, T_Equip_PM_Readings.Equip_ID, L_PM_Interval_UOMs.PM_Interval_UOM, T_Equip_PM_Readings.PM_Interval_UOM_ID
FROM (L_Locations INNER JOIN (((T_Equip_PM_Readings INNER JOIN L_PM_Types ON T_Equip_PM_Readings.PM_Type_ID = L_PM_Types.PM_Type_ID) INNER JOIN L_Equip ON T_Equip_PM_Readings.Equip_ID = L_Equip.Equip_ID) INNER JOIN L_PM_Interval_UOMs ON T_Equip_PM_Readings.PM_Interval_UOM_ID = L_PM_Interval_UOMs.PM_Interval_UOM_ID) ON L_Locations.Location_ID = L_Equip.Location_ID) INNER JOIN L_Equip_Makes ON L_Equip.Equip_Make_ID = L_Equip_Makes.Equip_Makes_ID
WHERE (((T_Equip_PM_Readings.PM_Interval_UOM_ID)=1) AND ((Val([meter_value])-Val([pm_meter_value]))>=[pm_interval]));

Union
SELECT 0 AS Due_Date, L_Equip_Makes.Equip_Make, T_Equip_PM_Readings.Equip_PM_Def_ID, L_Locations.Location, L_Equip.Equip_Descr, L_PM_Types.PM_Type, T_Equip_PM_Readings.Meter_Value, T_Equip_PM_Readings.PM_Meter_Value, Val(meter_value)-Val(pm_meter_value) AS run_hours, T_Equip_PM_Readings.PM_Interval, T_Equip_PM_Readings.Equip_ID, L_PM_Interval_UOMs.PM_Interval_UOM, T_Equip_PM_Readings.PM_Interval_UOM_ID
FROM (L_Locations INNER JOIN (((T_Equip_PM_Readings INNER JOIN L_PM_Types ON T_Equip_PM_Readings.PM_Type_ID = L_PM_Types.PM_Type_ID) INNER JOIN L_Equip ON T_Equip_PM_Readings.Equip_ID = L_Equip.Equip_ID) INNER JOIN L_PM_Interval_UOMs ON T_Equip_PM_Readings.PM_Interval_UOM_ID = L_PM_Interval_UOMs.PM_Interval_UOM_ID) ON L_Locations.Location_ID = L_Equip.Location_ID) INNER JOIN L_Equip_Makes ON L_Equip.Equip_Make_ID = L_Equip_Makes.Equip_Makes_ID
WHERE (((T_Equip_PM_Readings.PM_Interval_UOM_ID)=4) AND ((Val([meter_value])-Val([pm_meter_value]))>=[pm_interval]));

Union
SELECT 0 AS Due_Date, L_Equip_Makes.Equip_Make, T_Equip_PM_Readings.Equip_PM_Def_ID, L_Locations.Location, L_Equip.Equip_Descr, L_PM_Types.PM_Type, T_Equip_PM_Readings.Meter_Value, T_Equip_PM_Readings.PM_Meter_Value, Val(meter_value)-Val(pm_meter_value) AS run_hours, T_Equip_PM_Readings.PM_Interval, T_Equip_PM_Readings.Equip_ID, L_PM_Interval_UOMs.PM_Interval_UOM, T_Equip_PM_Readings.PM_Interval_UOM_ID
FROM (L_Locations INNER JOIN (((T_Equip_PM_Readings INNER JOIN L_PM_Types ON T_Equip_PM_Readings.PM_Type_ID = L_PM_Types.PM_Type_ID) INNER JOIN L_Equip ON T_Equip_PM_Readings.Equip_ID = L_Equip.Equip_ID) INNER JOIN L_PM_Interval_UOMs ON T_Equip_PM_Readings.PM_Interval_UOM_ID = L_PM_Interval_UOMs.PM_Interval_UOM_ID) ON L_Locations.Location_ID = L_Equip.Location_ID) INNER JOIN L_Equip_Makes ON L_Equip.Equip_Make_ID = L_Equip_Makes.Equip_Makes_ID
WHERE (((T_Equip_PM_Readings.PM_Interval_UOM_ID)=5) AND ((Val([meter_value])-Val([pm_meter_value]))>=[pm_interval]));

Union
SELECT DateAdd(‘m’,[pm_interval],[pm_metervalue]) AS Due_Date, L_Equip_Makes.Equip_Make, T_Equip_PM_Readings.Equip_PM_Def_ID, L_Locations.Location, L_Equip.Equip_Descr, L_PM_Types.PM_Type, T_Equip_PM_Readings.Meter_Value, IIf([PM_Meter_Value]=”0″,’01/01/1900′,[pm_meter_value]) AS PM_MeterValue, DateDiff(‘m’,IIf([PM_Meter_Value]=”0″,’01/01/1900′,[pm_meter_value]),Date()) AS run_hours, T_Equip_PM_Readings.PM_Interval, T_Equip_PM_Readings.Equip_ID, L_PM_Interval_UOMs.PM_Interval_UOM, T_Equip_PM_Readings.PM_Interval_UOM_ID
FROM (L_Locations INNER JOIN (((T_Equip_PM_Readings INNER JOIN L_PM_Types ON T_Equip_PM_Readings.PM_Type_ID = L_PM_Types.PM_Type_ID) INNER JOIN L_Equip ON T_Equip_PM_Readings.Equip_ID = L_Equip.Equip_ID) INNER JOIN L_PM_Interval_UOMs ON T_Equip_PM_Readings.PM_Interval_UOM_ID = L_PM_Interval_UOMs.PM_Interval_UOM_ID) ON L_Locations.Location_ID = L_Equip.Location_ID) INNER JOIN L_Equip_Makes ON L_Equip.Equip_Make_ID = L_Equip_Makes.Equip_Makes_ID
WHERE (((DateDiff(‘m’,IIf([PM_Meter_Value]=”0″,’01/01/1900′,[pm_meter_value]),Date()))>=[pm_interval]) AND ((T_Equip_PM_Readings.PM_Interval_UOM_ID)=2));

Union
SELECT DateAdd(‘ww’,[pm_interval],[pm_metervalue]) AS Due_Date, L_Equip_Makes.Equip_Make, T_Equip_PM_Readings.Equip_PM_Def_ID, L_Locations.Location, L_Equip.Equip_Descr, L_PM_Types.PM_Type, T_Equip_PM_Readings.Meter_Value, IIf([PM_Meter_Value]=”0″,’01/01/1900′,[pm_meter_value]) AS PM_MeterValue, DateDiff(‘ww’,IIf([PM_Meter_Value]=”0″,’01/01/1900′,[pm_meter_value]),Date()) AS run_hours, T_Equip_PM_Readings.PM_Interval, T_Equip_PM_Readings.Equip_ID, L_PM_Interval_UOMs.PM_Interval_UOM, T_Equip_PM_Readings.PM_Interval_UOM_ID
FROM (L_Locations INNER JOIN (((T_Equip_PM_Readings INNER JOIN L_PM_Types ON T_Equip_PM_Readings.PM_Type_ID = L_PM_Types.PM_Type_ID) INNER JOIN L_Equip ON T_Equip_PM_Readings.Equip_ID = L_Equip.Equip_ID) INNER JOIN L_PM_Interval_UOMs ON T_Equip_PM_Readings.PM_Interval_UOM_ID = L_PM_Interval_UOMs.PM_Interval_UOM_ID) ON L_Locations.Location_ID = L_Equip.Location_ID) INNER JOIN L_Equip_Makes ON L_Equip.Equip_Make_ID = L_Equip_Makes.Equip_Makes_ID
WHERE (((DateDiff(‘ww’,IIf([PM_Meter_Value]=”0″,’01/01/1900′,[pm_meter_value]),Date()))>=[pm_interval]) AND ((T_Equip_PM_Readings.PM_Interval_UOM_ID)=3));

The union query clauses select records based on different criteria based on the Unit of Measure for the particular piece of equipment.  Some equipment has a calendar based PM schedules while others have numerical schedules such as Engine Run Time or Pump Back Pressure.

More Union Queries on Our Website

Union Query Downloadable Example
Our Union Query Example demonstrates a method used to add additional … Click here to download the Microsoft Access Union Query programming example.

Advanced Union Query Download
This union query example shows some interesting and advanced concepts. Usually in a union query you are selecting the same or similar data from different…

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
Contact Information

Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials

Access Database Templates:

    Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016