How to calculate the number of working hours each month in a Access query?

[wire_jp]

Hi All,

I am using MS Access 2010. I am working out each member of staffs working hours for the month in a MS Access query.

[WorkDate]
[TimeIn]
[TimeOut]
minus a 1 hour lunch.

I would like to display the total hours worked for the month in a hours : minutes format.

I tried this approach: -
Call a field WorkHoursPerMin in a query:-.

WorkHoursPerMin: Sum(DateDiff("n",[timeIn],[timeOut]))

Make a GroupBy query and Sum this field.

For the display I used a second query based on the first where I did the following:

WorkHoursPerMonth: CDate([WorkHoursPerMin]\60 & ":" & [WorkHoursPerMin] Mod 60)

My idea is to show a column with the month of the year and a second with the number of hours worked in that month.

Your help with this is greatly appreciated.

 

[arnelgp]

if the time your recording is straight time, meaning they don't punch out during lunch break and punch in after lunch (minus 1 hours):
deduct one hour (60 mins).

WorkHoursPerMin: Sum(DateDiff("n",[timeIn],[timeOut])-60)

are you getting the right result?

 

[wire_jp]

Hi Arnelgp,

Thank you for your prompt response. My answer is "yes". When I test for a time interval of 16 hours in a month, the result is 960 minutes (i.e. 16 *60 minutes), testing for 8 hours in the next month, the result is 480 minutes (i.e 8 * 60 minutes).

However, the result in 2nd query, when I use the function (N.B. This function is "Group By" as a Total):-

WorkHoursPerMonth: CDate([WorkHoursPerMin]\60 & ":" & [WorkHoursPerMin] Mod 60)

The result for the 1st month: 4:00:00 PM
The result for the 2nd month: 8:00:00 AM

My understanding of these results is for result of the 1st month meant that 4:00 pm mean 16 hours have occurred (if time is measured over a 24 hour period). Similarly the result of 8:00 am is 8 hours (starting from midnight).

I want to show the results of the 2nd query as:
1st month - 16 hours
2nd month - 8 hours
/How do I achieved the above desired results?

 

[wire_jp]

I just found a solution:

WorkMths: [WorkHoursPerMin]\60 & Format([WorkHoursPerMin] Mod 60,"\:00")

Thank you, Arnelgp for your help. I greatly appreciate you taking the time to help me.

 

[wire_jp]

Hi Arnelgp,

I have attached my database below. I have a follow-up question: - in the query qryEmployeeHoursPerMonth", I wanted to add a field column for the "MonthoftheYear" e.g. January [MonthoftheYear] has 8 working hours per month [WorkHoursPerMonth]. Is it possible to add a field column called [MonthoftheYear]
[URL=http://s1068.photobucket.com/user/wirejp/media/Relationships.png.html][/URL]

[URL=http://s1068.photobucket.com/user/wirejp/media/qryEmployeeHoursPerMonth.png.html][/URL]

 

[arnelgp]

you can put it in a new table:
FieldName: MonthNo (byte, 1-12)
FieldName: YearNo (Long, 2016, 2017, etc)
FieldName: HrsPerDay (integer, 8, etc)

then on your query:

WorkHoursPerMonth: Dlookup("HrsPerDay", "theAboveTableName", "MonthNo = " & Month([WorkDate]) & " And [YearNo] = " & Year([WorkDate]) )

 

[wire_jp]

Hi Arnelgp,

I created a table called "tblMonths":
FieldName: MonthID (Autonumber)
FieldName: MonthNo (byte, 1-12)
FieldName: YearNo (Long, 2016, 2017, etc)
FieldName: HrsPerDay (integer, 8, etc)

I linked this tblMonths table (PK:MonthID) to the tblTimeTracker table (FK:MonthID)

In my query called "qryEmployeeHoursPerMonth", I added:

WorkHoursPerMonth: DLookUp("HrsPerDay",[tblMonths],"MonthNo = " & Month([WorkDate]) & " And [YearNo] = " & Year([WorkDate]))

When I run the query, a parameter box appears: "Enter the Parameter Value?" for tblMonths
followed the error message:-
This expression is typed correctly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.


Thanks,

wire_jp

 

[wire_jp]

I got the problem resolved. I made a mistake in the function where I put [tblMonths] instead of "tblMonths". The query return a result of 8.