Need Union query??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I have 2 created queries:
Query 1 is qry_Sum_for_Comparison_PP and contains the following fields:
PP_Shift_Date
PP_Shift (i.e. 1,2,3)
PP_Mold (text)
SumOfPP_Earn_Hrs

Query 2 is qry_Sum_for_Comparison_LI and contains the following fields:
LI_Shift_Date
LI_Shift (i.e. 1,2,3)
LI_Mold (text)
SumOfHours_Worked

What I ultimately want is a query that returns all the values from Query 1
and Query 2 whether they match or not so I tried a UNION query on the first 3
fields which gave me the data I needed but I also need the 4th field from
each query which as you can see is different.

The following 6 fields is what I ultimately want to see (example of how some
data would look):

PP_Shift_Date PP_Shift PP_Mold LI_Mold SumOfPP_Earn_Hrs SumOfHours_Worked
11/28/05 1 1572 1572 6.18 5.67
11/28/05 1 1872 15.77
11/28/05 1 2008
0.53

Is there help for me?
Thanks, Stacey
 
Add a placeholder field to each part of the UNION (or add them to Qry1 and
Qry2).

SELECT PP_Shift_Date, PP_Shift, PP_Mold, SumOfPP_Earn_Hrs, *Null AS
SumOfHours_Worked* FROM Qry1
UNION SELECT LI_Shift_Date, LI_Shift, LI_Mold, *Null AS SumOfPP_Earn_Hrs*,
SumOfHours_Worked FROM Qry2
(*...* mark the placeholders for effect. You wouldn't really enter the *s)

The above creates separate EarnedHrs and WrkdHrs fields. Your example shows
separate PPMold and LIMold fields, while I use one field for both. If you
really want them separate (any given record will never have both filled in),
just create additional placeholders. Just make sure you have the same # of
fields in both parts of the UNION and that they are in the same order.

I almost always add a "Source" field to each section of my Union queries:
"PP AS Source,..." & "LI AS Source,...". It can make subsequent manipulation
of the data in reports, etc. a lot easier. Depends on what you do with it.

HTH,
 
George,
Thanks, I have a little better understanding of the union query now.
I created the extra field I needed in each query by doing
LI_Earned_Hours: 0
PP_Hours_Worked: 0
Problem is I try to do a math equation based on this information and get
nothing.
My data results now look like this:
Date Shift Mold Earn Hrs Hrs Worked
11/28/2005 1 1572 0.00 5.67
11/28/2005 1 1572 6.18 0
11/28/2005 1 1872 15.77 0
11/28/2005 1 2008 0.00 0.53
Which is one step closer then I change from "Group By" to Sum and it doesn't
sum it stays like this. So I create a query off of this to do the sum and it
worked in the new query, I am afraid it is not recognizing the 0 I put in for
LI_Earned_Hours: 0
PP_Hours_Worked: 0 as numbers - is there something I should do?
Thanks again!
 
Back
Top