If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum Query
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. |
#2
|
|||
|
|||
Sum Query
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 '================================================= === Ernst Guckel wrote: 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. |
Thread Tools | |
Display Modes | |
|
|