I've been beating my head against this issue for a while now, and haven't been able to find a solution via Google. I'd like to be able to total the number of hours worked by an employee in a given department, since some employees work in different departments depending on the day. I'm querying an Access mdb, but it's not my software so I can't make any changes to the DB. I have the following relevant fields in the table:
DepartmentID,
EmployeeID,
PunchDateTime,
PunchType (boolean: True=In, False=Out)
Is there a way to pull out the sums of hours worked, grouped by departments and employees? If it can be done in SQL, that'd be awesome, otherwise if it can be manipulated in Excel that'd be fine also. Here's what I've tried, it puts it in the right format, but the results don't jibe with the software's built-in reports:
SELECT
DepartmentID,
EmployeeID,
Sum((IIf(PunchType=True,-1,1))*DateDiff("n",DateDiff("d",0,PunchDateTime),P unchDateTime))/60 AS HoursWorked,
FROM TimesTable
WHERE ((PunchDateTime>=#1/4/2011#)
AND (PunchDateTime<=#30/4/2011#))
GROUP BY DepartmentID, EmployeeID
ORDER BY DepartmentID, EmployeeID;
Thanks for any help!