Results 1 to 10 of 10
  1. #1
    infratunes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2018
    Posts
    4

    Question Sum of worked hours

    Dear all,

    I would like to sum the total working hours for a particular day and employee ID. The problem is that during a same day, an employee can clock-in and out several times, which creates duplicate records in my table.

    How can I sum the total working hours in order to get a single line in an Access Query?



    I have provided an example of the expected result in the file here attached.

    Kind regards,
    William.

    Query_question.txt

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,791
    I would use two queries.

    The first query would calculate the worked hours per line, then a 2nd query (totals query) to get the total worked hours and minutes.

    For EID 77, you have a column named "Break" with a value of "1:30". What does this value represent? It is written as a time, but is it a total break amount of 1 hour and 30 minutes? (anything with a colon is a time, a duration would be hours and minutes).
    If you subtract an ending time from a starting time you get hours and minutes.

    I would think you should have

    1st Query
    Employee ID
    Date Line Code Start End Break Worked Hours
    77 2018/08/03 1 WRK 08:30 18:19 1hr 30 min 8.32 hrs or 8 hrs and 53 min
    77 2018/08/03 2 WRK 18:31 19:06 0.58 hrs or 35 minutes
    Write a UDF to calculate the worked hours


    Totals Query result
    Employee ID
    Date Worked Hours
    77 2018/08/03 9 hrs and 28 minutes

    I hope you do not have a field name of "Date". It is a reserved word in Access and a built in function.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,457
    misread requirements. Post removed.

  4. #4
    infratunes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2018
    Posts
    4
    Hi Steve,

    Thanks for your reply.

    The Column break is actually irrelevant for the calculation. the break time is already subtracted from the total worked hours in the excel report that I get.

    Since I am am novice, how do you write a UDF to sum the working hours into one single line?

    (I do not have any Date field in my report. Thanks anyway for highlighting this point).

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,791
    This is the simplest function (UDF) to calculate duration from two times.
    Put this in a standard module
    Code:
    Public Function fCalcWorkHours(pStartTime As Variant, pEndTime As Variant)
        fCalcWorkHours = Round(DateDiff("n", pStartTime, pEndTime) / 60, 2)
    End Function
    The first query would be
    Code:
    SELECT WorkHours.EmployeeID, WorkHours.WrkDate, WorkHours.WrkStart, WorkHours.WrkEnd, fCalcWorkHours([WrkStart],[WrkEnd]) AS WkHrs
    FROM WorkHours;
    Note: it doesn't appear that the break time was taken into consideration in the text file example records!


    The total query would be
    Code:
    SELECT [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate, Sum([qryCalcWrkHours].WkHrs) AS SumOfWkHrs
    FROM qryCalcWrkHours
    GROUP BY [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate;
    -------------------------------------------------------------------------------------------------------------------------
    Query results
    Click image for larger version. 

Name:	Calcs1.png 
Views:	22 
Size:	141.9 KB 
ID:	35936
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    infratunes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2018
    Posts
    4
    Quote Originally Posted by ssanfu View Post
    This is the simplest function (UDF) to calculate duration from two times.
    Put this in a standard module
    Code:
    Public Function fCalcWorkHours(pStartTime As Variant, pEndTime As Variant)
        fCalcWorkHours = Round(DateDiff("n", pStartTime, pEndTime) / 60, 2)
    End Function
    The first query would be
    Code:
    SELECT WorkHours.EmployeeID, WorkHours.WrkDate, WorkHours.WrkStart, WorkHours.WrkEnd, fCalcWorkHours([WrkStart],[WrkEnd]) AS WkHrs
    FROM WorkHours;
    Note: it doesn't appear that the break time was taken into consideration in the text file example records!


    The total query would be
    Code:
    SELECT [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate, Sum([qryCalcWrkHours].WkHrs) AS SumOfWkHrs
    FROM qryCalcWrkHours
    GROUP BY [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate;
    -------------------------------------------------------------------------------------------------------------------------
    Query results
    Click image for larger version. 

Name:	Calcs1.png 
Views:	22 
Size:	141.9 KB 
ID:	35936

    Thank you for your reply!

    However, I am getting the following error message:

    Click image for larger version. 

Name:	Capture4.JPG 
Views:	17 
Size:	7.2 KB 
ID:	35937

    Here is my current setup:

    Click image for larger version. 

Name:	Capture1.JPG 
Views:	18 
Size:	22.2 KB 
ID:	35938
    Click image for larger version. 

Name:	Capture2.JPG 
Views:	17 
Size:	10.3 KB 
ID:	35939
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	17 
Size:	37.4 KB 
ID:	35940

    Any idea regarding the issue?

    thanks.

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,257
    you have the same name for both module and function - suggest change the module name to say modCalcWorkHours

  8. #8
    infratunes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2018
    Posts
    4
    Fixed! Thank you to both of you!

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,791
    From both of us, you are welcome.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    Join Date
    Apr 2017
    Posts
    1,117
    Let's assume you have a table tblHoursLog with fields EmployeeID, LogDate, StartTime (in format Date/Time, "hh:mm"), EndTime (in format Date/Time, "hh:mm"), Break (in format Date/Time, "hh:mm")

    You get working hours by employees and dates with query (I created 2 return values - WorkTime you can format as "hh:mm" in your form or report, WorkHrs returns the number or working hours - select one you need)
    Code:
    SELECT LogDate, EmployeeID, SUM(EndTime-StartTime-Nz(Break,0)) AS [WorkTime], SUM(24*(EndTime-StartTime-Nz(Break,0))) AS [WorkHrs]
    FROM tblHoursLog
    GROUP BY EmployeeID, LogDate;

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

Similar Threads

  1. Replies: 2
    Last Post: 11-09-2015, 01:24 PM
  2. Replies: 3
    Last Post: 06-23-2014, 11:51 AM
  3. Replies: 8
    Last Post: 03-01-2013, 11:55 AM
  4. calculate rate * hours worked
    By hamish mather in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 06:14 AM
  5. Sum hours worked, grouped by dep't
    By goodfood in forum Queries
    Replies: 3
    Last Post: 05-05-2011, 06:11 AM

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