Results 1 to 4 of 4
  1. #1
    goodfood is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Posts
    2

    Question Sum hours worked, grouped by dep't

    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!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,904
    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?

  3. #3
    goodfood is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Posts
    2
    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!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,904
    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.
    Last edited by orange; 05-05-2011 at 06:13 AM. Reason: spelling

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 12-15-2010, 01:44 PM
  2. workinkg hours by percent
    By Mosely in forum Queries
    Replies: 1
    Last Post: 11-01-2010, 09:32 AM
  3. Access 2010 fails where 2007 worked
    By dick in forum Access
    Replies: 3
    Last Post: 10-16-2010, 01:20 AM
  4. Grouped Tables
    By tmcrouse in forum Queries
    Replies: 0
    Last Post: 09-24-2009, 07:10 AM
  5. hours + condition
    By Miriam in forum Queries
    Replies: 0
    Last Post: 08-09-2009, 06:46 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums