Problem with UNION query

[diddydustin]

Hi guys, I have the following query:

SELECT
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No,
Sum(EMR_Graphic_TimeTable.Time_Total)
AS SumOfTimeTotal
FROM
EMR_Graphic_TimeTable
WHERE
(((DateValue(DateAdd("n",990,
[ttDate]+[ttTime])))=Date()))
GROUP BY
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No;

UNION

SELECT
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No,
Sum(EMR_Graphic_TimeTable.Time_Total)
AS SumOfDownTotal
FROM
EMR_Graphic_TimeTable
WHERE
(((DateValue(DateAdd("n",990,
[ttDate]+[ttTime])))=Date()))
AND ((EMR_Graphic_TimeTable.Time_Code <> 900)
AND (EMR_Graphic_TimeTable.Time_Code <> 100))
GROUP BY
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No;

I am having trouble because only the first query is used-- it won't return SumOfDownTotal. What am I doing wrong? Is there a better way to do this query? I am using it in a complex report where I need to show the total time, and then various time totals by their code. I was going to do multiple queries for each time code and UNION them together. There are around 3 queries for the different codes now, however, there is a possiblity they may want the codes broken down even further.

Any help would be appreciated
Dustin

 

[dodge20]

I think what you want is a Union All query. A union eliminates duplicate data between queries. From what I see, you want this query

SELECT
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No,
Sum(EMR_Graphic_TimeTable.Time_Total)
AS SumOfTimeTotal
FROM
EMR_Graphic_TimeTable
WHERE
(((DateValue(DateAdd("n",990,
[ttDate]+[ttTime])))=Date()))
GROUP BY
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No;

UNION ALL

SELECT
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No,
Sum(EMR_Graphic_TimeTable.Time_Total)
AS SumOfDownTotal
FROM
EMR_Graphic_TimeTable
WHERE
(((DateValue(DateAdd("n",990,
[ttDate]+[ttTime])))=Date()))
AND ((EMR_Graphic_TimeTable.Time_Code <> 900)
AND (EMR_Graphic_TimeTable.Time_Code <> 100))
GROUP BY
EMR_Graphic_TimeTable.Time_Code,
EMR_Graphic_TimeTable.Job_No;


Dodge20

 

[diddydustin]

Dodge20,

I tried that but it still doesn't work. It shows what fields are returned from the query and it is still only the first; the SumOfDownTotal doesn't show. Any more ideas? I've looked into UNION queries but I am still having trouble.

Dustin