calculate difference between rows

[sammers101]

Trying to calculate the hours my employees have worked. The table format I'm using can't be altered, its created by our pos program.

For example I have
fldUserID fldTime fldType fldDate
blake 12:04:38 PM Begin Shift 6/3/2018
blake 6:31:44 PM End Shift 6/3/2018
sam 9:22:06 AM Begin Shift 6/4/2018
James 5:03:16 PM Begin Shift 6/4/2018
sam 5:33:59 PM End Shift 6/4/2018
James 8:03:16 PM Lunch Out 6/4/2018

I thought I could do something like timeclock.id=timeclock_1.id+1
but people dont neccessarily clock in and out like that.

 

[plog]

First, I don't believe you when you say the table can't be altered. How is data getting from your pos program into Access? Is it a linked table? If not, then you need to set up your table properly and that means combining the date and time fields.

After you accomplish getting the Date/Time together you then need to create a query to find the 'next' Date/Time for every record that signifies a beginning. You would do that with either a DMin (https://www.techonthenet.com/access/functions/domain/dmin.php) or correlated subquery (https://en.wikipedia.org/wiki/Correlated_subquery) to find what Date/Time occurs after each record.

Once you have that you can use DateDiff (https://www.techonthenet.com/access/functions/date/datediff.php) to calculate the total time between the two Date/Times.

 

[MarkK]

Take plog's advice. Then, one trick with shifts is use -1 to indicate punch-in date/time rows, and +1 for punch-out date/time rows. Then, to sum shift lengths, use SQL like...

SELECT Sum(PunchInOut * PunchDateTime) As SumShiftLength
FROM <table>
WHERE UserID = <user>

This yields a negative date/time for a punch in, a positive date/time for a punch out, and the sum of those is the length of the shift. It does this for however many punches there are in the result set. And, obviously, you can add date/time constraints to the WHERE clause too.

To error check, if your result is wildly negative, like < -10000, then you are missing a punch out. If your result is wildly positive, like > 10000, then you are missing a punch in.

hth
Mark

 

[pbaldy]

If your times don't cross midnight, you can use a totals query, grouping on the employee and date, and returning the min and max times.

 

[Mark_]

In a query you can put date and time together.

As your table is
fldUserID
fldTime
fldType
fldDate

You can do a query with
FldUserID
fldType
DateAndTime: DateValue(FldDate) + TimeValue(FldTime)

This would give you a query that would return your data as

"blake" "Begin Shift" "6/3/2018 12:04:38 PM"
"blake" "End Shift" "6/3/2018 6:31:44 PM"
"sam" "Begin Shift" "6/4/2018 9:22:06 AM"
"James" "Begin Shift" "6/4/2018 5:03:16 PM"
"sam" "End Shift" "6/4/2018 5:33:59 PM"
"James" "Lunch Out" "6/4/2018 8:03:16 PM"

In reality, the last would be something like "James" "Lunch Out" "44395.766522" as a DateTime is really a number.

You could then order by FldUserID and DateAndTime to get your transactions in order.