Why do we need union queries?

[The_Doc_Man]

I'll try to offer a reason why union queries are helpful.

Imaging a company selling stuff with multiple sources for that stuff, multiple wholesale and retail customers, and a shrinkage plan for damaged, lost, or pilfered stuff, and even perhaps an archival checkpoint (where you remove really old records to save space and just put a summary record to maintain continuity.). You would have two choices.

1. Make every one of those things fit into a single record (which might be tricky since the shrinkage data and the wholesale data might not fit well together), OR

2. Make each data class have its own record format - but then to figure our inventory of stuff on hand, you use UNION queries that select common elements like the "stuff" inventory number, date of transaction, and number of items of "stuff" covered by the transaction. The UNION query can even make stuff "gains" (arrival of new stock, stock returns, etc.) have the opposite sign to stuff "losses" (customer purchases, shrinkage, etc.). Then your stock on hand at any date is merely the sum of all UNION records with dates on/before the selected date. Which gives you the ability to do running inventory totals, stock turnover rate, etc.

Case 2 is where UNION queries can shine when done well.

 

[gemma-the-husky]

The DOCMan's post above is very insightful. Needing to use a union query often does represent less than optimal design. Actively deciding to use a structure that requires the use of union queries clearly represents a "considered" approach.

---------------------------

another use of a union query is to add an <ALL> option to a combo box source.

say the combo box row source is

select ref, detail from sometable

but you want an "ALL" option

then

select ref, detail from sometable
union
select -1, "<ALL>"

will give a rowsource that achieves that.

 

[The_Doc_Man]

Good one, Dave. I use other ways to achieve "ALL" because in my case, the customer's style is to ASSUME you meant ALL but then you can click a toggle button that makes visible a combo box for selection of individual options. You way would certainly work.

As to "non-optimum design" - I might quibble VERY SLIGHTLY. The case I described is not a non-optimum design but rather is a real-world, non-optimum data model, where the choice is to either do "square-peg-in-round-hole" tables OR to let the real-world data be your table design guide and then just extract the pieces-parts from each table to match what you really needed. Of course, with the union query, you can include constants for fields to show what kind of transaction occurred, as

select InvNumber, -Qty, "PURCHASE" from tblPurchases
union
select InvNumber, Qty, "SHIPMENT" from tblShipments
union
select InvNumber, -Qty, "SHRINKAGE" from tblShrinkage
etc. etc. etc.

 

[gemma-the-husky]

Doc_Man

I agree. What I meant was it was possible, even easy to end up with a situation that needs union queries when you didn't want it, and when they aren't the most helpful (because they are non-updateable). Purposefully designing a system that intends to use union queries is different entirely.

In your example, it may well make more sense to have 3 tables for purchases, shipments and shrinkage, than to try to coerce them into a single table.