Microsoft Access Union Queries (Part 1)
Union Queries are available in Microsoft Access, however they cannot be created by using conventional means (for example, Using the QBE Grid). These queries are super-powerful and surprisingly easy to create considering there’s no magical way to step through building them with any kind of wizard.
In Part 1 of this article, we'll examine some common questions in regards to Union Queries, and then in Part 2, we'll step through a how-to!
Why can’t they be created like any other query?
Union Queries are written only in SQL, and unfortunately, the QBE Grid (the usual grid you see when you are creating queries and dragging fields around) has no way to visually represent a Union Query, so it simply has SQL View and Datasheet View available when they are created. Union Queries serve only a small portion of needs considering different queries that can be created with the QBE Grid, so this issue isn’t really a big deal in the overall scheme of working a database.
What are Union Queries used for?
Union Queries are used to bring together two recordsets of data to merge into one recordset of data. For instance, let’s say you have two tables, one for sales going to individuals, and one for sales going to companies. A union query can bring all of the records from both tables (providing you are querying the same number of fields) into one giant recordset so you can view all of your records at once.
This is useful because although you may want to keep tables separate because they may pertain to different departments, bringing them together into one big query will allow you to run different statistical numbers across all of your sales. You would easily be able to compare the percentage difference in corporate clients to individuals in any region, or perhaps see where your greatest individual sales base is in order to target corporations in the same area.
Union Queries can also be used to create a single source for a mailing list. Union Queries eliminate the need to create a make-table query in order to bring in some records, then an append query to add others on top which bloats the size of your database as you’re storing all this data twice – once in their own tables, and once merged in a new table – which means you’ll have to deal with deleting specific data or updating only certain data and creating new object after new object in your database.
Union Queries are just like other queries, they don’t take up the space of a table, and the query is always updated to reflect new table data. Just like other queries, Union Queries can be used for report record-sources as well! Great stuff!
Can a form be based on a Union Query?
Technically, yes. However, Union Queries do not create updateable recordsets, so you can’t change any of the data and you can’t add new records. Using it as a record source for a conventional data-entry form won’t do you much good.
However, there are more uses for forms than just data entry, so it is certainly possible. Union Queries are best used in order to create reports. Report data doesn’t need to change or be updated, and you won’t be adding new records through a report, so it works well. Union Queries are mainly there to get a different kind of look at your data anyway, which is exactly what reports are for.
I generally keep similar information together in the same table, how would this apply to me?
The practical uses of Union Queries can be debated considering different types of database designs and normalization rules. The real-world example would be to apply Union Queries to data archives.
Let’s say you have a table that stores sales information – about 5,000 new records per week. Instead of keeping this entire table intact, giving you over 200,000 records by year’s end, you archive information quarterly. Since the archived tables have the same structure as the current tables, it’s easy to bring them all together with a Union Query in order to create calculations on all of your older data while keeping your current sales table small enough to help with overall database performance.
So in essence, Union Queries are something you should try to take advantage of when you need to get a little boost in the types of statistics you get out of a database. If you need to merge archived data, or just display a large amount of similar data, using this technique will make it quick and easy!
©2010 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: