Union query with separate fields

  • Thread starter Thread starter catherine
  • Start date Start date
C

catherine

I'm trying to merge 3 different queries (qrytest,
qrytest2, qrytest3)into one--for reporting purposes. Each
query is matched on a specific ID but has a separate field
based on that ID. For example, qrytest has field Nm,
qrytest2 has Nm2, and qrytest3 has Nm3; Nm in qrytest may
have ID as 1, Nm2 in qrytest2 may be null, and Nm3 in
qrytest3 may have 1. I've tried Union but the result has
one field. I'd like to have:

ID Nm Nm2 Nm3
1 (data) (data).

Instead, I'm getting:

ID Nm
1 (data)
1 (data)

Thanks for any tips.
 
Dear Catherine:

In order for the UNION to have four columns in its results, each SELECT
query that makes up the UNION must have those same four columns.

So, it might look like this:

SELECT ID, Nm, NULL AS Nm2, NULL AS Nm3
FROM qrytest
UNION ALL
SELECT ID, NULL AS Nm, Nm2, NULL AS Nm3
FROM qrytest2
UNION ALL
SELECT ID, NULL AS Nm, NULL AS Nm2, Nm3
FROM qrytest3

The results of this query are probably not what you want for a final result,
however. You will need an aggregate on ID in order to put these into a
single row for each ID.

Designing this will depend on what kinds of values are in Nm, Nm2, and Nm3.
I am guessing these are numeric values of some kind.

You will need to pick an aggregate function that will return the value in
qrytest for Nm, qrytest2 for Nm2, and qrytest3 for Nm3.

I would suggest you change the UNION query above to use zero for the
"placeholder" values above, like this:

SELECT ID, Nm, 0 AS Nm2, 0 AS Nm3
FROM qrytest
UNION ALL
SELECT ID, 0 AS Nm, Nm2, 0 AS Nm3
FROM qrytest2
UNION ALL
SELECT ID, 0 AS Nm, 0 AS Nm2, Nm3
FROM qrytest3

Now you can base an aggregation using SUM on the above. Adding zero to a
value results in that value. Adding zero to NULL results in NULL, so this
will return what you were wanting. If you save the UNION query above as
MyUnion, then you can write:

SELECT ID, SUM(Nm) AS Nm,
SUM(Nm2) AS Nm2, SUM(Nm3) AS Nm3
FROM MyUnion
GROUP BY ID

Please let me know if this helped, and if I can be of any other assistance.
 
I've got a question. Does each of the qryTest have all the ID's in it? If so,
why not do an Inner Join on the three queries and a totals query. Or did I
misunderstand completely?
 
Actually each of the qryTest is the result of a Left Join--
so they do not have all the ID's in it. I had to do a
Left Join based on the original table which contained ID's
of those who were scheduled for training, against a table
which had history of training (some ID's were not included
due to no training history). So the breakdown became each
of the qryTest. Does this seem understandable this way?
 
Back
Top