Solved Payroll Query (1 Viewer)

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

No I do not pay them for lunch,

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.

 

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

 

[Pat Hartman]

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.

 

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