Multiple queries -> one report?

[diddydustin]

Hey guys,

I'm using the following query:

SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
UNION
SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, 0
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code Not In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder
;


However, this query gives me one column of SUM values for time codes either 100, 900, or 0. In my report, I want to be able to break this down and have three columns, one for each time code. How can I do this?

Thank you
Dustin

 

[dhookom]

This sql should replace all the nulls with 0.

TRANSFORM Val(Nz(Sum([Sum Of Time_Total]),0)) AS TheValue
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Yes, that is what I am currently using. I don't think i'm fully explaining myself. That is not the problem-- the problem is I am replacing all nulls with 0 and STILL getting an error. Do you know anything else I should try?

I am using this query:
TRANSFORM Val(Nz(Sum([Sum Of Time_Total]),0)) AS TheValue
SELECT Employee, Sum([Sum Of Time_Total]) AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);


With these results:
Employee Downtime Makeready Run Time
Andy Dietrich 33840 19080 78120
Ben Lane 0 0 13680
Bo Brandenburg 96480 31680 78840
Darrell McKinney 367740 32940 249120
David Carter 116220 0 297720
Derek Enlow 106200 139680 352800
Earl McCoy 39600 0 39600
Glenn Suel 16560 8280 26280
Greg Goins 88560 0 282240
Harvey Farris 2160 26640 0
James Gorman 357120 0 0
Jerry Carter 76980 11520 282240
Jerry Colyer 2160 5040 0
Jerry Renner 10800 124920 297360
Mark Loudermilk 1440 111600 57960
Mike Eldridge 16560 8280 26280
Monte Stamper 18720 13320 26280
Robert Adams 3600 11520 13680
Robert E. Adams 13320 15120 60840
Shane Franklin 2820 0 25920
Sonny Preston 10440 0 47160
Steve Burton 96480 31680 78840


Trying this in a textbox:

=[Downtime]/3600
#Error

=CInt([Downtime])/3600
0

I need the real calculation, not a 0 or an #Error!

Thanks
Dustin

 

[dhookom]

What is the name of the text box? It can't also be the name of a field.

You could also do the division in your query:
TRANSFORM Val(Nz(Sum([Sum Of Time_Total]),0))/3600 AS TheValue
SELECT Employee, Sum([Sum Of Time_Total])/3600 AS AllTimes
FROM EMR_UnionTest INNER JOIN EMR_TimeCodeList ON EMR_UnionTest.Time_Code = EMR_TimeCodeList.Code
GROUP BY Employee
PIVOT IIf(EMR_UnionTest.[Time_Code]<100,"Downtime",EMR_TimeCodeList.[Desc]);



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Great!!!! Finally a good foundation for some reports. My final question is this: what is the most simplistic way for me to allow an end-user to select this data based on either the date, machine, name, or employee type, exclusively and inclusively? Do I need to create a form to select the values and a button to run the report? Or is there a better approach to working with the query that normalized EMR_EmployeeHistory we discussed earlier in the thread? I'm going to be doing the same type of report with different data multiple times and I want to figure out the best method to query based on date, machine, name, etc...


Thank you so much for your help! This has been one long thread.

Dustin

 

[dhookom]

Always create a form or forms with controls for users to enter criteria values.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Great, thank you so much for your help!!

Dustin