Sum hours worked, grouped by dep't

[goodfood]

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!

[orange]

I tried your code. I don't see any error. It will Sum by Employee, or Sum by Department.
Perhaps the software's built in reports are doing something, or assuming something that's not too obvious???
When you say they don't jibe, what exactly is different?

[goodfood]

OK, so I'm not crazy. I can't remember where I found that neat little sum-iif statement, but when I look at it, it makes perfect sense, but running it doesn't bring up the same numbers as the reports. For instance, the last 2 week period I worked 33 hours, but the SQL comes back with 118.

I'm wondering if the program grabs a recordset from the db, and then runs some kind of next() loop against it to do the math. I'm not sure how that's any different than a SUM, but it's the only thing I can see it doing. Thanks for the reply, though!

[orange]

After some thinking the only thing I can think of is that on the last day of April, any time reported will make the date > April 30. So, for the last day of the month the hours will not be included in that month's totals.
To correct for this your Between must be >= First of Month AND < First Of Next Month.

See if your hours on last day of the month make up the " don't jibe" amount.