Sum of worked hours

[infratunes]

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

[ssanfu]

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.

[alansidman]

misread requirements. Post removed.

[infratunes]

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).

[ssanfu]

This is the simplest function (UDF) to calculate duration from two times.
Put this in a standard module

Public Function fCalcWorkHours(pStartTime As Variant, pEndTime As Variant)
    fCalcWorkHours = Round(DateDiff("n", pStartTime, pEndTime) / 60, 2)
End Function

The first query would be

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

SELECT [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate, Sum([qryCalcWrkHours].WkHrs) AS SumOfWkHrs
FROM qryCalcWrkHours
GROUP BY [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate;

-------------------------------------------------------------------------------------------------------------------------
Query results

[infratunes]

This is the simplest function (UDF) to calculate duration from two times.
Put this in a standard module

Public Function fCalcWorkHours(pStartTime As Variant, pEndTime As Variant)
    fCalcWorkHours = Round(DateDiff("n", pStartTime, pEndTime) / 60, 2)
End Function

The first query would be

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

SELECT [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate, Sum([qryCalcWrkHours].WkHrs) AS SumOfWkHrs
FROM qryCalcWrkHours
GROUP BY [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate;

-------------------------------------------------------------------------------------------------------------------------
Query results

Thank you for your reply!

However, I am getting the following error message:



Here is my current setup:





Any idea regarding the issue?

thanks.

[CJ_London]

you have the same name for both module and function - suggest change the module name to say modCalcWorkHours

[infratunes]

Fixed! Thank you to both of you!

[ssanfu]

From both of us, you are welcome.

[ArviLaanemets]

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)

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;