Solved Payroll Query

[slharman1]

I have a table that collects clock-in and clock-out "punches" for my employees to do Job tracking, the employee clock's in on a work order, then clocks out on that work order, then Clock's in on a new work order all through the day. so each entry in the timeclock table has two entries - an "in" and an "out".
I need to develop a query that I can get the "in" field for the first record in a given day and the "out" field for the last record for the day and total the hours worked for the day in a group of days selected by selecting a start and end date on a calendar., the calendar for is easy enough, but I think I need some help with developing the query.
Thanks

 

[plog]

Its best to demonstrate data issues with data. So please provide 2 sets:

A. Starting data from your table(s). Include table and field anmes and enough data to cover all cases.

B. Expected results of A. Show us what data you hope to end up with when you feed in the data from A.

Again, 2 sets of data---starting data and expected data based on starting.

 

[oleronesoftwares]

I need to develop a query that I can get the "in" field for the first record in a given day and the "out" field for the last record for the day and total the hours worked for the day in a group of days selected by selecting a start and end date on a calendar., the calendar for is easy enough, but I think I need some help with developing the query.

for earliest clock in each day
SELECT clockinout.clockeddate, Min(clockinout.clockintime) AS MinOfclockintime
FROM clockinout
GROUP BY clockinout.clockeddate;

for last clock out in each day
SELECT clockinout.clockeddate, Max(clockinout.clockouttime) AS MaxOfclockoutime
FROM clockinout
GROUP BY clockinout.clockeddate;

 

[Gasman]

for earliest clock in each day
SELECT clockinout.clockeddate, Min(clockinout.clockintime) AS MinOfclockintime
FROM clockinout
GROUP BY clockinout.clockeddate;

for last clock out in each day
SELECT clockinout.clockeddate, Max(clockinout.clockouttime) AS MaxOfclockoutime
FROM clockinout
GROUP BY clockinout.clockeddate;

Wouldn't you need the employee in there somewhere?

 

[oleronesoftwares]

field for the last record for the day and total the hours worked for the day in a group of days selected by selecting a start and end date on a calendar

Wouldn't you need the employee in there somewhere?

The OP is silent on that, number of hours worked by a group of people(as long as a member of the group/staff is still working in each day is what is to be calculated, but your suggestion is very valid.

 

[pbaldy]

for earliest clock in each day
SELECT clockinout.clockeddate, Min(clockinout.clockintime) AS MinOfclockintime
FROM clockinout
GROUP BY clockinout.clockeddate;

for last clock out in each day
SELECT clockinout.clockeddate, Max(clockinout.clockouttime) AS MaxOfclockoutime
FROM clockinout
GROUP BY clockinout.clockeddate;

Other thoughts: you can get both values in the same query, and subtract to get elapsed time. A big potential "gotcha" here is if the day can cross midnight (I start on an order at 23;30 and finish at 01:15). That can grab you a couple of different ways, just something to keep in mind.

 

[slharman1]

Its best to demonstrate data issues with data. So please provide 2 sets:

A. Starting data from your table(s). Include table and field anmes and enough data to cover all cases.

B. Expected results of A. Show us what data you hope to end up with when you feed in the data from A.

Again, 2 sets of data---starting data and expected data based on starting.

A. Starting Data Table
tblTimeClock
Fields: TimeClockID - AutoNumber, WONumber - Short Text(not needed for this), TimeIn - Date/Time, TimeOut - Date/Time, EmpID - Number

I have attached an excel file (tblTimeClockData) with the data and field names for A. above

As far as B. goes the final output would look something like the Excel file (tblPayrollData) attached but I don't have to create a table for it, a recordset will work (or something I can run a report on)

Wouldn't you need the employee in there somewhere?

yes I do.

The OP is silent on that, number of hours worked by a group of people(as long as a member of the group/staff is still working in each day is what is to be calculated, but your suggestion is very valid.

I definitely need each employee

 

[CJ_London]

I have attached an excel file

don't see it

 

[slharman1]

don't see it

I guess the post posted itself while I was away from the keyboard.
Here is the data file

 

[plog]

Again, 2 sets of data A and B. Using the spreadsheet as A. Now provide B. Show me the data you expect the query to output based on A.

 

[slharman1]

I guess the post posted itself while I was away from the keyboard.
Here is the data file

I am modifying the test data. Will re upload the data sheet and will include an output sheet with it. Might take me a bit.
thank you.

 

[slharman1]

So, you pay people while they are out to lunch?

It is far better to use a single in/out record. It is easy to reconcile and you can prevent people from logging in again until they log out the previous record. This also avoids the "overnight" issue with shift work. You can also prevent them from logging out if they haven't logged in.

Fix the data early. It is far easier than having to fix it at payroll time.

No I do not pay them for lunch, but this is in conjunction with them clocking in on works orders and so I am trying to pick the first clock-in and the last clock-out for the day in order to get the payroll hours as well as the order tracking.

The setup I have now is the one you don't like where I set the properties for the subform in the load event of the main form and you said it should be on the subform. I haven't been able to get it to work on the subform, so it is still there.

They must clock-out of one work order before clocking in on a new one.
I think I can work through all of the time issues once I can get the data coming the way I want it.

Don't give up on me now - I may not understand what you are asking - I could figure out a way to have the initial clock-in and the last clock-out not be part of the work order tracking "punches" I guess but that seems like it adds another level of difficulty.

 

[slharman1]

Again, 2 sets of data A and B. Using the spreadsheet as A. Now provide B. Show me the data you expect the query to output based on A.

Here is the Time Clock Data table and the way I envision the Payroll Data ( I did not calculate the times correctly on the payroll sheet because I didn't know how to do it in excel) I am open to any formatting that makes the calculations easier. The Time Worked column on the payroll sheet is shown how I figured Hrs:Min:Sec would be but like I said my calculations are fictitious.
Thanks

 

[slharman1]

So, you pay people while they are out to lunch?

It is far better to use a single in/out record. It is easy to reconcile and you can prevent people from logging in again until they log out the previous record. This also avoids the "overnight" issue with shift work. You can also prevent them from logging out if they haven't logged in.

Fix the data early. It is far easier than having to fix it at payroll time.

Your lunch comment has me thinking - right now when they clock-out on a work order my app displays a message box "Are you going home?" with a yes/no. If no, they clock in on another work order if yes it closes the time punch form and opens the dialog for anyone to scan an employee badge and start the process over again. So as I am writing this I realized the lunch issue has solved itself - they will select going home for lunch punch.

 

[slharman1]

So, you pay people while they are out to lunch?

It is far better to use a single in/out record. It is easy to reconcile and you can prevent people from logging in again until they log out the previous record. This also avoids the "overnight" issue with shift work. You can also prevent them from logging out if they haven't logged in.

Fix the data early. It is far easier than having to fix it at payroll time.

I must not understand what you are saying but I could add another form with Monday through Sunday for them to clock the payroll hours. Just seems redundant.

 

[slharman1]

Then you have to match the individual in/out punches and calculate the blocks of time and accumulate the blocks. It is possible to have more than one break during a work day. Maybe in addition to lunch, someone had to run out and pick up a child who missed his bus home.
Clocking in/out should not include a work order. That is how you tell the punches apart from those for tracking a work order.

Please see the reply I just posted. Is that what you are guiding me to?

 

[slharman1]

Don't try to tie actions together. Each WO should have a separate "card" or whatever they are swiping. You can help with login/logout for the day but do not chain work orders. There will be time during the day, if workers are honest, when they are not working on a work order. I have lots of stories about the system I built to do this but won't bore you with the trials of dealing with union rules and the worker who logged 5 hours to change the light bulb in my office.

So when they swipe their badge have a pop up for time sheet OR work order?

 

[slharman1]

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.

 

[plog]

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.

 

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

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!