Solved Payroll Query (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
to clock the payroll hours. Just seems redundant.
You are assuming that the hourly workers are ALWAYS working on a work order. What do they charge meetings or training to? Are special work orders created for ALL possible events? Are standing ones reused? So everyone charges training1 regardless of who they are and what training they are taking?

If your business rules are that all time is tracked by work order and none is unaccounted for, that's fine and that means that you don't have to worry about plain in/out punches. The earliest in is the start of the day but you still track time, work order by work order. If a person worked on 4 WOs during the day and took a lunch break during WO2, you would have WO1 in/out. WO2 In/out, WO2 in/out, WO3 in/out, and WO4 In/out. Now the only problem is aligning the punches with a shift so the after midnight WOs get logged to the correct day.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
I'm going away now so take a breath and see if you can sort out our crossed messages.
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
You are assuming that the hourly workers are ALWAYS working on a work order. What do they charge meetings or training to? Are special work orders created for ALL possible events? Are standing ones reused? So everyone charges training1 regardless of who they are and what training they are taking?

If your business rules are that all time is tracked by work order and none is unaccounted for, that's fine and that means that you don't have to worry about plain in/out punches. The earliest in is the start of the day but you still track time, work order by work order. If a person worked on 4 WOs during the day and took a lunch break during WO2, you would have WO1 in/out. WO2 In/out, WO2 in/out, WO3 in/out, and WO4 In/out. Now the only problem is aligning the punches with a shift so the after midnight WOs get logged to the correct day.
Your first paragraph is spot on, I have a Work order for "Shop Time" which is for meetings, training, or anything else not attributed to a work order.

I am not chaining the WOs together - they can clock-out of a work order and not clock back in on another one until later, that wouldn't matter if I collect the first punch-in of the day and the last punch-out. But now you got me thinking about the after normal hours work.

We are a small company with 25 employees in the shop. So that was the plan, but if you think that is not a good idea, I am open to suggestions. ;)


Also, I am trying to figure out the after midnight issue because although we only run one shift, I do have employees (installers) that occasionally work from say 9pm until 7am or so the following day, on a case by case basis (depending on job requirements) so any advanced direction to handle those pitfalls would very much appreciated.
 
Last edited:

plog

Banishment Pending
Local time
Today, 08:08
Joined
May 11, 2011
Messages
11,613
Luckily, I've read no one elses conjectures about what your data is or is not. I've only looked at your actual data. The table itself looks good, and I am hoping you just got sloppy on the expected report---the TimeWorked seems off a lot, like you didn't do exact math.

If that's the case you need a query to base your Report off of:

Code:
SELECT tblTimeClock.ID AS TimeClockID, tblTimeClock.TimeIn, tblTimeClock.TimeOut, tblTimeClock.EmpID, DateDiff("s",[TimeIn],[TimeOut]) AS SecondsWorked, Format((DateDiff("s",[TimeIn],[TimeOut])/86400),"hh:nn:ss") AS TimeWorked
FROM tblTimeClock;

Since you want time worked down to the second, you use DateDiff to calculate that between your two times. That field is listed above as [SecondsWorked], I've also included [TimeWorked] formatted as you wanted.

Then in a report you use that query as its datasource and then add heading and grouping areas as needed. The Period Total will be in a Group Footer of the report. You will Sum up the SecondsWorked values and use the Format code I used in the query to display it properly.
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
Luckily, I've read no one elses conjectures about what your data is or is not. I've only looked at your actual data. The table itself looks good, and I am hoping you just got sloppy on the expected report---the TimeWorked seems off a lot, like you didn't do exact math.

If that's the case you need a query to base your Report off of:

Code:
SELECT tblTimeClock.ID AS TimeClockID, tblTimeClock.TimeIn, tblTimeClock.TimeOut, tblTimeClock.EmpID, DateDiff("s",[TimeIn],[TimeOut]) AS SecondsWorked, Format((DateDiff("s",[TimeIn],[TimeOut])/86400),"hh:nn:ss") AS TimeWorked
FROM tblTimeClock;

Since you want time worked down to the second, you use DateDiff to calculate that between your two times. That field is listed above as [SecondsWorked], I've also included [TimeWorked] formatted as you wanted.

Then in a report you use that query as its datasource and then add heading and grouping areas as needed. The Period Total will be in a Group Footer of the report. You will Sum up the SecondsWorked values and use the Format code I used in the query to display it properly.
plog, you are correct my expected report is not actual calculations, they are just similar to what I want the data to show me for payroll purposes based on a period of one week. So time collected from Monday at say 5:30am until the following Monday at 5:29am would constitute a work week.

Also, I do not care about seconds, that is just how access is recording the time in/out records and I am not sure about messing with the format in Date/Time fields - or is that worry only in building the tables?

I like your solution and will give it a try, I'll be in touch.
Thanks a bunch!
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
Luckily, I've read no one elses conjectures about what your data is or is not. I've only looked at your actual data. The table itself looks good, and I am hoping you just got sloppy on the expected report---the TimeWorked seems off a lot, like you didn't do exact math.

If that's the case you need a query to base your Report off of:

Code:
SELECT tblTimeClock.ID AS TimeClockID, tblTimeClock.TimeIn, tblTimeClock.TimeOut, tblTimeClock.EmpID, DateDiff("s",[TimeIn],[TimeOut]) AS SecondsWorked, Format((DateDiff("s",[TimeIn],[TimeOut])/86400),"hh:nn:ss") AS TimeWorked
FROM tblTimeClock;

Since you want time worked down to the second, you use DateDiff to calculate that between your two times. That field is listed above as [SecondsWorked], I've also included [TimeWorked] formatted as you wanted.

Then in a report you use that query as its datasource and then add heading and grouping areas as needed. The Period Total will be in a Group Footer of the report. You will Sum up the SecondsWorked values and use the Format code I used in the query to display it properly.
One more question, do i want to add this qry to an employee query or table in order to get data such as employee name pay and to calculate overtime for anything over 40 hours in a work period?
 

plog

Banishment Pending
Local time
Today, 08:08
Joined
May 11, 2011
Messages
11,613
Question #1--Your fields are the correct datatype for your data, so you don't need to apply a format there.

Question #2--I don't know what you want to do with this query so that's up to you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
Either you need all time present to be assigned to a work order OR you need in/out punches to delineate start/end of work periods. Your company needs to make policy and it needs to be in writing because when you are dealing with a payroll, you will have fights on hand if you don't pay people just because they punched improperly. I would think it is safer to go with the punch in/out to calculate payroll. That way you won't be quibbling about a minute lost here or there between logging out of one WO and into a different one. The punches in/out are for payroll purposes. The punches for WO are for costing a job or internal calculation of overhead. Two different reasons. And my original inclination was to not combine them.

You really can't calculate payroll as the time between the first in and the last out for a shift. You calculate payroll by summing the elapsed time of pairs of punches for the shift.

Don't forget that the datetime data type is a POINT in time. It is not elapsed time. You shouldn't store elapsed time. You should always store in/out and calculate elapsed time in queries. Then you need to create a format function to sum the elapsed seconds and come up with hh:mm. You can sort of do it with a field defined as datetime but only as long as you NEVER go past 24 hours. So, if you ever have the potential to work more than 24 hours in an emergency, you'll need a custom calculation so you can end up with 25:32 an an elapsed time. I can post one if you need it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:08
Joined
Feb 19, 2013
Messages
16,555
if you are accounting for time, how are you accounting for the small gaps between punch-out of job#1 and punch-in of job#2?. From what I've seen of your other threads users scan their id, then scan the job card to punch-out, then (not sure) scan their id again and the next job card to punch-in. Might take a minute or so?
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
Either you need all time present to be assigned to a work order OR you need in/out punches to delineate start/end of work periods. Your company needs to make policy and it needs to be in writing because when you are dealing with a payroll, you will have fights on hand if you don't pay people just because they punched improperly. I would think it is safer to go with the punch in/out to calculate payroll. That way you won't be quibbling about a minute lost here or there between logging out of one WO and into a different one. The punches in/out are for payroll purposes. The punches for WO are for costing a job or internal calculation of overhead. Two different reasons. And my original inclination was to not combine them.

You really can't calculate payroll as the time between the first in and the last out for a shift. You calculate payroll by summing the elapsed time of pairs of punches for the shift.

Don't forget that the datetime data type is a POINT in time. It is not elapsed time. You shouldn't store elapsed time. You should always store in/out and calculate elapsed time in queries. Then you need to create a format function to sum the elapsed seconds and come up with hh:mm. You can sort of do it with a field defined as datetime but only as long as you NEVER go past 24 hours. So, if you ever have the potential to work more than 24 hours in an emergency, you'll need a custom calculation so you can end up with 25:32 an an elapsed time. I can post one if you need it.
Once again you have pointed me in the right direction :)

Please do post a calculation on how to handle punches that go over 24 hrs because I will have that situation.

But I am going to create a separate table for payroll.
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
Ok so now i am creating separate tables for calculating payroll hours.
I need to be able to clock-in monday -sunday (Monday is our first day of the week.
My form needs a period ending date automatically entered based on Sunday for the current week.
My employees will choose from a option frame with options for Mon-Sun, then use a command button to capture the current date/time.

There is the occasion where they will clock-in on Monday morning and clock-out on Tuesday morning.
I can't figure out how to get the "period ending" date for Sunday of the current week.
Please help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
I didn't say to create a separate table. It is not wrong but I wouldn't do it because it will be easier to have the data in a single table should you need both types of punches in a single report. If they are in separate tables, you would need a union query to bring them together.

Here is a link to a list of VBA functions by category.

Functions (category list) | Microsoft Docs
Bookmark it so you don't lose it. Print it out so it is always handy to scan until you get comfortable with knowing what functions are available. Where it is relevant, the date functions include arguments to specify the first day of the week.

Here's a link to a database I built that includes useful date functions and forms to show how they might be used. It has a function to format elapsed time to your specification.

Sample Date Functions | Access World Forums (access-programmers.co.uk)

I don't have anything to put dates into shifts. I'll think about it and if I have time, I'll add it to this date sample. In the meantime, you need a table that defines shifts. Usually there will be three but you can have as many as you want. You might have people working three 8-hour shifts per day but you might have some people working 10 hour shifts, etc. Then the record for each employee contains his assigned shift. If you need this to be variable, you will need a way to collect the shiftID in the login punch for the day. Since shifts change over time, I would copy the currently assigned shift to the login record -ALWAYS but give the user an opportunity to modify it for the day. That allows for the most flexibility since it allows a person normally working first shift to swap with a person working second shift just one time.
Add a watch to any sample you download from this forum so you will get a notification if something changes. The samples are rarely updated so don't worry about being inundated with useless emails.
 
Last edited:

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
I didn't say to create a separate table. It is not wrong but I wouldn't do it because it will be easier to have the data in a single table should you need both types of punches in a single report. If they are in separate tables, you would need a union query to bring them together.

Here is a link to a list of VBA functions by category.

Functions (category list) | Microsoft Docs
Bookmark it so you don't lose it. Print it out so it is always handy to scan until you get comfortable with knowing what functions are available. Where it is relevant, the date functions include arguments to specify the first day of the week.

Here's a link to a database I built that includes useful date functions and forms to show how they might be used. It has a function to format elapsed time to your specification.

Sample Date Functions | Access World Forums (access-programmers.co.uk)

I don't have anything to put dates into shifts. I'll think about it and if I have time, I'll add it to this date sample. In the meantime, you need a table that defines shifts. Usually there will be three but you can have as many as you want. You might have people working three 8-hour shifts per day but you might have some people working 10 hour shifts, etc. Then the record for each employee contains his assigned shift. If you need this to be variable, you will need a way to collect the shiftID in the login punch for the day. Since shifts change over time, I would copy the currently assigned shift to the login record -ALWAYS but give the user an opportunity to modify it for the day. That allows for the most flexibility since it allows a person normally working first shift to swap with a person working second shift just one time.
Add a watch to any sample you download from this forum so you will get a notification if something changes. The samples are rarely updated so don't worry about being inundated with useless emails.
I don't need shifts we only run one shift. And I can't see a reason i would ever need Work Order punches and time clock punches on the same report. You say it is not wrong so is it ok or better to do it in one table.
Then do I create different kinds of punches in one table, one field for clock-in, one for clock-out, one for clock-inOnWO and one for clock-outn WO?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
I don't need shifts we only run one shift.
I don't know what your business is or its future for growth. Don't poo poo ideas that expand flexibility. It is significantly easier to accommodate shifts now than it will be after the app is built should the company hit a growth spurt. So, if it is remotely possible, that your employer might want to add a shift during the lifetime of the application, do it now.
You say it is not wrong so is it ok or better to do it in one table.
I have indicated several times that I would have them in the same table. The payroll type have null in the WO field. That is the only difference and that is how you separate them using a query if you want the sets separated. Each pair of in/out punches is a separate record. You would never have multiple pairs of punches in a single record. WO and payroll punches have nothing whatsoever to do with each other so they would never exist as part of a single record. That would be a pathological connection.

If you are absolutely certain you would never need the two punch types in a single report/form, then put them in a separate table. I am not so certain (and I have a lot more experience in this area), so I would put them in the same table.
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
I didn't say to create a separate table. It is not wrong but I wouldn't do it because it will be easier to have the data in a single table should you need both types of punches in a single report. If they are in separate tables, you would need a union query to bring them together.

Here is a link to a list of VBA functions by category.

Functions (category list) | Microsoft Docs
Bookmark it so you don't lose it. Print it out so it is always handy to scan until you get comfortable with knowing what functions are available. Where it is relevant, the date functions include arguments to specify the first day of the week.

Here's a link to a database I built that includes useful date functions and forms to show how they might be used. It has a function to format elapsed time to your specification.

Sample Date Functions | Access World Forums (access-programmers.co.uk)

I don't have anything to put dates into shifts. I'll think about it and if I have time, I'll add it to this date sample. In the meantime, you need a table that defines shifts. Usually there will be three but you can have as many as you want. You might have people working three 8-hour shifts per day but you might have some people working 10 hour shifts, etc. Then the record for each employee contains his assigned shift. If you need this to be variable, you will need a way to collect the shiftID in the login punch for the day. Since shifts change over time, I would copy the currently assigned shift to the login record -ALWAYS but give the user an opportunity to modify it for the day. That allows for the most flexibility since it allows a person normally working first shift to swap with a person working second shift just one time.
Add a watch to any sample you download from this forum so you will get a notification if something changes. The samples are rarely updated so don't worry about being inundated with useless emails.
Love the Date functions, Thanks
One question - How do I change the first day of the week to Monday?
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
I don't know what your business is or its future for growth. Don't poo poo ideas that expand flexibility. It is significantly easier to accommodate shifts now than it will be after the app is built should the company hit a growth spurt. So, if it is remotely possible, that your employer might want to add a shift during the lifetime of the application, do it now.

my only concern with shifts is that an employee could punch in on one shift and then when they punch out it would be on another shift.
I am thinking that I can use the punc in time to determine which shift the record goes with. Does that sound correct?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
Did you look at the function link? The property is set in the relevant date functions. I didn't include any of that in he app. Look at the DateDiff() function since it does both first day of week and first week of year.

Windows "Region" settings has the option of making Monday the first day of the week. I don't know how this affects Access so you would need to test to see if this works for you, but, Access does not have a first day of week setting.

If you change the Windows setting, EVERYONE who uses your application MUST also change the setting so I am not sure I would rely on this. I would make use of the Weekday() function if I needed to make Monday the first day.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,981
The concept of shift is used to make the midnight to x punches belong to the day before when the shift started if the shift started the day before. That is all. I don't know of any other reason to care when the punches happened. If the shift is 4-12 and you work overtime, which day do the hours get associated with? If the week ends at midnight on Sunday, counting the hours as Monday might affect whether or not you get overtime.
 

slharman1

Member
Local time
Today, 08:08
Joined
Mar 8, 2021
Messages
467
The concept of shift is used to make the midnight to x punches belong to the day before when the shift started if the shift started the day before. That is all. I don't know of any other reason to care when the punches happened. If the shift is 4-12 and you work overtime, which day do the hours get associated with? If the week ends at midnight on Sunday, counting the hours as Monday might affect whether or not you get overtime.
One company policy is that the work week starts on Monday at 12:00 am and it ends on Sunday At 11:59, So I could have an employee that works Sunday from 9pm to Monday at 8am and that work would be on the tart time's work week as far as O/T is concerned. So their last day as far as the pay period is concerned would be from 9pm Sunday until Monday at 8am I am hoping the DateDiff Function will calculate correctly as far as the clock-in /out times are concerned in that scenario.
I'll have to test to see (but I'm sure you will chime in with the correct answer :))
Thanks again Pat
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:08
Joined
Feb 19, 2013
Messages
16,555
if you only have the time available you can use something like

datediff("n",starttime,endtime-(endtime<starttime))
 

Users who are viewing this thread

Top Bottom