SQL Union Queries

Here’s a solution to a small but tricky requirement in MS Access

“I want to find everyone who owns product XYZ but not ABC — but if they don’t fall into that category then those with PROD1 and/or PROD2 and/or PROD3.”

I resisted the urge to write this logic with Basic code. It would be “cleverer” to use queries and have the advantage that the client could take it over and tinker further. I’m all in favour of independent customers.

As always, the key is breaking down into stages. What I did was build two normal SELECT queries, then a third UNION query to glue the results. UNIONs are like taking the rows of one set of results and sticking them on the end of another. Both sets of results need the same number of columns, and maybe the same datatype too.

The first query looked like this:

The second query looked like this:

Note that they both extract the same columns of data, which is a pre-requisite for UNION.

Finally, join them with a UNION query. To make one of these, in Query Design View, click Query from the menu, then “SQL Specific -> Union”. Here you can type some SQL but not drag and drop in the usual way. My code was simple:

SELECT DISTINCT * FROM qryTradeupOwners_1
UNION SELECT DISTINCT * FROM qryTradeupyOwners_2
ORDER BY CustomerNum;