Need help doing Calculations in a Query 1

[mrdod]

I am trying to get some information about a teams performance (manufacturing). Basically I have two calcuated fields [Units Processed] and [Man Hours]. I need to divde these in order to get Units/Hour. Do I need to do a subquery in order to get this info? I read the help file and it says only applies to Microsoft Access Project (.adp). I am using .mdb. Of course I am pretty green with this so I'm sorry if I'm not supplying enough information.
Any info wold be great.


SELECT Sum(tblhistory.[Units Processed]) AS [Units Processed], Sum(tblhistory.[Total POTs]) AS [Total POTs], Sum(tblhistory.[Down Time]) AS [Total Down Time], Sum(tblhistory.[Shift Length]*tblhistory.[Total POTs]-tblhistory.[Down Time]) AS [Man Hours] Need to take [Units Processed/[Man Hours]
FROM tblhistory
HAVING (((tblhistory.[Date Entered]) Between [forms]![frmprodreport]![tbstartdate] And [forms]![frmprodreport]![tbenddate]) AND ((tblhistory.Line)=[Forms]![frmprodreport]![cmbline]) AND ((tblhistory.Shift)=[Forms]![frmprodreport]![cmbshift]));

I'm tring to get the following result
Units Processed Man Hours Units Worked
1000 60 16.67

 

[PHV]

Like this ?
SELECT Sum([Units Processed]) AS [Units Processed]
, Sum([Total POTs]) AS [Total POTs]
, Sum([Down Time]) AS [Total Down Time]
, Sum([Shift Length]*[Total POTs]-[Down Time]) AS [Man Hours]
, Sum([Units Processed])/Sum([Shift Length]*[Total POTs]-[Down Time]) AS [Units Worked]
FROM tblhistory
WHERE [Date Entered] Between [Forms]![frmprodreport]![tbstartdate] And [Forms]![frmprodreport]![tbenddate]
AND [Line] = [Forms]![frmprodreport]![cmbline]
AND [Shift] = [Forms]![frmprodreport]![cmbshift]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrdod]

Yup That's it. Thanks PH