Union Queries in Access
Have you ever wanted to combine similar data from
more than one table into one report? You have several options available to
you. You could use the Make Table Query and then subsequent Append Table
queries to get the desired data. While this does get the job done, it is
not very efficient and it tends to bulk up your database unless you are
able to compact it frequently. The second alternative is to look into
using a Union Query. A Union query takes data from two or more queries and
combines them into one result set. It is much more dynamic than the first
option since, once it is created and saved, it will give you results on
your current data without having to go back and recreate it.
We will walk through a scenario where you want to
create a Union Query and then go through the process of creating one.
You have been asked to create a mailing list that
includes all of your customers and all of your suppliers. You could
use a Make Table query and follow it up with an append query. However,
you were also told that this list would be used somewhat frequently.
The best way to accomplish your list is to create a Union Query.
The first step will be to create your first query.
Let’s start with the Customers table. Open up the Northwinds database and
click on the query tab. Click New and bring in the Customers table. Bring
in the ContactName, Address, City, Region, PostalCode, and Country.
Minimize this query for later reference and do the same for the Suppliers
in a new query. Minimize this new query and bring back the first query you
created. Before we continue, save this query as MailingList.
Your Query should now look something like this:
*****
Important Note *****
A Union
query will only work if all of the fields in both queries are the same.
Now that we have our queries created, we are ready to
delve into the finer points of a Union Query.
From your MailingList query, Select View and then
change the view to SQL view. You will see the SQL representation of your
query. Highlight the entire section of code and copy it (Ctrl + C).
Next click on Query and go to SQL Specific and you
will see Union (See Below) on the resulting drop down menu. Select it and
you will have a new blank window pop up.
In your new window, paste the data you copied from
your initial query and delete the semicolon at the end of the string. Next
press <Enter> and type the word “Union” and press <Enter> again. Bring
your second query back up and view it in SQL view as well. Copy the SQL
string from it and paste it in the new line of your MailingList query.
Your query should now look like this:
Save your query and execute it. You should see all 91
of your customers and all 29 of your suppliers in one large table.
You have now run your own Union Query! Remember, a
Union query is not updateable as a select query is, but it is dynamic in
that it will change as you change your original data. It also has
the advantage of not bulking up your database as repeated use of temporary
tables can do. There are limitations of course, as all fields in all of
the queries involved must be the same. If you have the need to use data in
this manner, this is an ideal way to do so.
I hope you have enjoyed this month’s walk through
Union Queries and I look forward to hearing from you about what you would
like to see in the future!
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
,
