Uses For UNION Queries
There are many uses for UNION queries in SQL. You may be familiar with the
UNION query from other database management systems and believe that they are
not available in an Access database, but they are.
In fact, you can use most SQL statements natively in Access by opening a
new query and not selecting any tables for the query. You'll
immediately see the familiar blue triangle that represents design view has
been replaced by SQL.
Image that you have several queries with complex selection parameters.
Each of the resultant datasets is unique and cannot be combined into a
single SELECT query. Nonetheless, you'd like to export them to Excel for
analysis all at once instead of doing each query separately.
You can open each of the queries and extract their SQL to paste into a
UNION query. Open a query and then click the design view/datasheet view
icon's drop-down arrow.
You can see there's more here than just design view and datasheet view!
Click on the SQL view button. Copy the SQL you see there but leave off
the semicolon (;) you see at the end.
Now open a new query and decline to choose any source tables. Go into
SQL view again and select all of the text that's there so that we can
paste over it thereby deleting it.
For readability, hit the enter key, type the word UNION and hit the
enter key again.
Now go to the other queries you'd like to show up in the same view and
paste their SQL into this query separating each by the UNION keyword.
After the last query, don't place the word UNION, but do leave the
semicolon indicating the end of the command. Save your query and try it
out in datasheet view.
It's very important that every one of the contributing queries has the
same number of columns and, if you plan to make any sense at all of the
resultant dataset, that the columns line up! In other words, if your
first query asks for
DateOfTransaction Amount
Customer
don't UNION it with a query that asks for
Amount Customer
DateOfTransaction
The UNION query will work but it won't be worth looking at!
Another use for the UNION query is to add a label or title to a dataset.
A simple example is a query that feeds a combobox on a form. Imagine
that you allow the user to select from all available products in your
parts list. But you also want to allow the choice of N/A. You may be
tempted to create an imaginary part with the name of N/A in order to get
that into your combo box but you don't have to!
Use the UNION keyword in your combobox query and simply add in 'N/A'
before the rest of the part list.
SELECT 'N/A' As PartName, 0 as
PartNumber FROM PartsList
UNION
SELECT PartName, PartNumber FROM PartsList;
You can use the same method to insert titles or explanatory text into
your queries as they are being run.
|
|