Solved Payroll Query

[slharman1]

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:

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]

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.

 

[CJ_London]

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]

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]

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!

 

[slharman1]

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?

 

[slharman1]

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]

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?

 

[slharman1]

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]

if you only have the time available you can use something like

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

 

[CJ_London]

if end time is less than start time it has to be the following day

either endtime<starttime or it's not. If it is less that starttime, it's true which is -1

so if end time is less that start time you have endtime--1=endtime+1

in minutes
?datediff("n",#19:03:45#,#23:00:00#-(#23:00:00#<#19:03:45#))
237
?datediff("n",#19:03:45#,#01:00:00#-(#01:00:00#<#19:03:45#))
357

obviously if the person has worked over 24 hours then the answer will be wrong - which is why always better to include the date and not just the time

 

[slharman1]

Going live for testing with all employees tomorrow!
I want to give a big shout out to the forum and all the great help I keep receiving from this forum!
Pbaldy
Plog
Gasman
CJ_London
And I won’t forget are arnelp from other posts I’ve made, if I am forgetting anyone it is not because I do not appreciate it, you’ve all been so helpful!
I want want to say special thanks goes to Pat Hartman for getting me through this! Your name will be on my splash screen for providing the time/date functions.
thank you all!
I am sure this is not over but a great hurdle has been overtaken and I couldn’t have done it without you all.
Sincere thanks to all!

 

[CJ_London]

perhaps that is down to rounding? - haven't checked but think datediff for hours works on the hours in the time - in this example 21-20

?datediff("h",#20:59:51#,#21:00:00#)
1
?datediff("h",#20:01:51#,#21:00:00#)
1

 

[CJ_London]

Use an example with date and time, not just time.

I did say 'if you only have the time available you can use something like'

If using datetime and not just time, you don't need the adjustment as you well know. I had the impression OP was only recording the time since they seemed to have a problem calculating duration when the time was following day.

 

[slharman1]

How do I put this code in a query OR
How can I

Private Sub Form_Current()
   
    If IsNull(txtAdjTimeIn) And Not IsNull(txtTimeIn) Then
       
        Dim t1 As Date
        Dim t2 As Date
        t2 = DateValue([txtTimeIn]) + TimeSerial(Hour([txtTimeIn]), Minute([txtTimeIn]), 0)
       
        t1 = TimeSerial(0, 15, 0)
       
            If Minute(t2) = 30 Or Minute(t2) = 15 Or Minute(t2) = 0 Or Minute(t2) = 45 Then
            [txtAdjTimeIn] = t2
            Else
            [txtAdjTimeIn] = RoundUpToNearestDate(t2, t1)
            End If
        'txtAdjTimeIm = roundDate(txtTimeIn, 3)
    End If
   
        If IsNull(txtAdjTimeOut) And Not IsNull(txtTimeOut) Then
       
        Dim t3 As Date
        Dim t4 As Date
        t4 = DateValue([txtTimeOut]) + TimeSerial(Hour([txtTimeOut]), Minute([txtTimeOut]), 0)
       
        t3 = TimeSerial(0, 15, 0)
       
        [txtAdjTimeOut] = RoundDnToNearestDate(t4, t3)
       
        'txtAdjTimeIm = roundDate(txtTimeIn, 3)
    End If
   

   
End Sub

 

[pbaldy]

You can't. You can create a public function that takes the 2 values as parameters and returns the result, and call that from a query.