Home  Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles  Search  Contact  Privacy  Links
Union Query   Access Union Query
Home > Tutorials > Query Tutorials > Union Query
 





Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Insert Into Query
Master Detail Update
Order By Query
Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Select Top
Union Query
Update Query

 

MultiSelect Parameter Forms Query Tutorial


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

 

 


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.

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







A Blue Claw Database Design Template:

Subscription Order Control Database
 









A Blue Claw Database Design Article:

Why Choose Microsoft Access
 









Blue Claw Database Design Downloadable Tutorial:
How To Program Continuous-Continuous Master/Detail Forms  








Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
 




Contact Information

Copyright 2000-2014 Blue Claw Database Design