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 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.

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.

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

 

 

---

More Union Query Examples:

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. www.blueclaw-db.com/download/union_query.htm

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 www.blueclaw-db.com/download/union_query_advanced.htm