Sum Query

[Ernst Guckel]

Hello,

I am trying to sum all hours worked per employee within a given week. I
want the total of EmpHours and EmpOTHours between Date1 and Date2 Here is
what I have but of cource it doesnt work right...

SELECT DISTINCTROW tblEmployees.EmpLast & ", " & [EmpFirst] AS FirstLast,
Format$(tblEmpPayroll.EmpPayDate,'Long Date') AS [EmpPayDate By Day],
Sum(tblEmpPayroll.EmpHours) AS [Sum Of EmpHours],
Sum(tblEmpPayroll.EmpOTHours) AS [Sum Of EmpOTHours]
FROM tblEmployees INNER JOIN tblEmpPayroll ON tblEmployees.EmpFileNumber =
tblEmpPayroll.EmpNumber
GROUP BY tblEmployees.EmpLast & ", " & [EmpFirst],
Format$(tblEmpPayroll.EmpPayDate,'Long Date'), tblEmployees.EmpFirst,
tblEmpPayroll.EmpNumber
HAVING (((Format$([tblEmpPayroll].[EmpPayDate],'Long Date')) Between
Format(Date()-Weekday(Date()),"\#mm/dd/yyyy\#") And
Format(Date()-Weekday(Date())-7,"\#mm/dd/yyyy\#")));


Thanks,
Ernst.

 

[John Spencer]

PERHAPS something like the following. Although without knowing your
table structure (tblEmpPayroll) and what data is actually stored in the
table this is a guess.

SELECT tblEmployees.EmpLast & ", " & [EmpFirst] AS FirstLast,
Format$(tblEmpPayroll.EmpPayDate,'Long Date') AS [EmpPayDate By Day],
Sum(tblEmpPayroll.EmpHours) AS [Sum Of EmpHours],
Sum(tblEmpPayroll.EmpOTHours) AS [Sum Of EmpOTHours]
FROM tblEmployees INNER JOIN tblEmpPayroll
ON tblEmployees.EmpFileNumber = tblEmpPayroll.EmpNumber
GROUP BY tblEmployees.EmpLast & ", " & [EmpFirst],
Format$(tblEmpPayroll.EmpPayDate,'Long Date')
, tblEmployees.EmpFirst,
tblEmpPayroll.EmpNumber
WHERE [tblEmpPayroll].[EmpPayDate] Between
Date()-Weekday(Date())
And Date()-Weekday(Date())-7


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================