Date difference of every 2nd record in a field

[UnlucksMcGee]

Hey guys,
So I've got a table with a field of date-times. It is the clock-in/clock-out times of an employee, in one list. (e.g. clock-in when he arrives at work, clock-out for break, clock back in again, and then clock-out at the end of the day. He may take more than 1 break. (There is no data specifying if a certain datetime is a clock-in or clock-out event). All that is certain, is that every odd record is a clock-in event, and every even record is a clock-out event, in the field.


So what I'm struggling with, is how to calculate the total hours worked. Something along the lines of ([record 2]-[record 1]) +([record 4] - [record 3])....
Or in order to make computation easier:
- [record 1] + [record 2] - [record 3] + [record 4]....
I already have 2 separate queries, the one contains every odd datetime record, and the other contains every even datetime record. (Not sure if using these 2 queries is the right approach to solving this problem)
I appreciate any help, or hints to help me in the right direction.
Thanks!

[John_G]

To start, you might want to rethink your data structure. I would suggest that each record contain a time-in AND a time-out field. This might sound like it is not properly normalised, but not so. Time-in and time-out are diifferent things - they are not repeating occurances of the same data element. You would need a bit of work to determine whether a punch-time is in or out (not too difficult), and you would also need to figure out how to handle cases where an employee forgets to clock in or out.

But the first thing will be to get the record structure straight.

[ssanfu]

I totally agree with Joe_G.... structure is everything. Get it right first, then develop the queries/forms.

<snip>All that is certain, is that every odd record is a clock-in event, and every even record is a clock-out event, in the field.<snip>

1) What happens if the employee forgets to clock out at the end of the day (had to report to the boss, for an emergency)???

2) is a "clock-in" and/or "clock-out" a "Date AND Time" or just times?
3) What are you doing with the duration? Saving it into a table? Same table or different table?


---------------------------------------------------------------

<snip>
Or in order to make computation easier:
- [record 1] + [record 2] - [record 3] + [record 4]....
<snip>

This won't work. You cannot add two times to get a duration.

Example:

record 1: clock-in at 8:00am
record 2: clock-out at 9:30am

record 3: clock-in at 10:00am
record 4: clock-out at 12:00pm


If you try replacing "- [record 1]" with the time of #8:000am# and "+ [record 2]" with #9:30am#, the result is "1:30:00 AM". But "1:30:00 AM" is a time, not a duration.
However, if you subtract two times, you get a duration (as a percentage of a day).
If you use ([record 2]-[record 1]) with the times above, you get 0.0625. Multiply that by 24 hours and you get 1.5 hours.


So, the calculation would be:
(([record 2]-[record 1])*24) + (([record 4] - [record 3])*24) + .........

[SodaPop]

Just calculate on the fly, less work. Table with name - function - date / time - year- week- elapsed.

When clocked check record by name and current date, if null clock in, if not null check last clock, if in clock out else clock in. Finally once you know which clock, if clocking out, do the math and plunk into the table with an insert Sal statement.

You can the just pull emp, year, week and sum on elapsed. If You want you could add day easily too.

If you need more specifics holler.

Sent from my SM-G950U using Tapatalk

[UnlucksMcGee]

(I'm on mobile, so can't format my replies nicely, sorry)
@John_G
Sorry for delayed response, posted this late at night. Unfortunately for this situation, I can't put them in separate fields, as we have tried in the past, but employees mix up the clock-in/clock-out buttons, and it just makes a big mess to fix

@ssanfu
1) I have a separate form that handles and does all the checking, and allows editing of the individual time clock-in/outs. So for the sake of this post, all the times are already correctly inputted.
2) Date and time
3) The duration(s) will be exported to an excel spreadsheet, where it is further manipulated. For this case, a "duration" of 01:30AM is sufficient, as the excel spreadsheet handles it (splitting by ":" and some maths to get "1.5" hours)
With regards to your calculations, I could modify my calculation to account for the hours as such:
(-[record 1]+[record 2] - [record 3] + [record 4] + .....)*24
(assuming even amount of records, which can be assumed in this case, as it is edited manually to ensure it is)
But as I said above, the *24 is unnecessary, as it is handled in the excel spreadsheet (which is the payroll system, it requires a lot of work to move to Access, but for now it's in Excel)

@SodaPop
That's a nice different way of approaching it, but it has one flaw. It doesn't allow the employee to forget to clock-in/out, or sometimes clock-in twice (which happens often).

@Everyone
One solution is to take the single field of alternating clock-in/clock-outs, and split it into 2 separate fields, one containing the clock-ins and the other containing the clock-outs. But I'm not sure how to do this, as none of the "Join" types (outer/inner/cartesian) can be used as they require an extra field to match up the clock-in/clock-out times correctly.

[UnlucksMcGee]

UPDATE:
(As I don't have a internet connection for my laptop, I can't provide any screenshots, so sorry in advance)
I've found a solution, but I'm not sure how reliable it is, as for me it seems like a hack-solution, and not really something simple.

Step 1: I have a query that takes the table with the field of datetimes (field_datetime), and adds an extra field to it (called field_pairing), with values: 1,1,2,2,3,3,4,4 ... Such that each clock-in and clock-out are paired together with the same value in this field. This query then stores the 2 fields into a new table (called tbl_temp).

Step 2: Then I have 2 separate queries (qry_0 and qry_1) which have their source as the new "tbl_temp". These two queries add another field (called field_0or1), which generates alternating values of 0 and 1, and filters the records such that qry_0 only contains the records where field_0or1 = 0, and vice-versa for qry_1. So now qry_0 (clock-in times) looks like: field_datetime, field_pairing (with values 1,2,3,4...), field_0or1 (with value 0)
and qry_1 (clock-out times) look like: field_datetime, field_pairing (with values 1,2,3,4..., field_0or1 (with value 1).

Step 3: Now I have an additional query (qry_join) that joins qry_0's and qry_1's field_datetime with the criteria that qry_0's and qry_1's field_pairing are equal. This makes qry_join look like: field_datetime (clock-in times), field_datetime (clock-out times), field_pairing (1,2,3,4...), field_pairing (1,2,3,4...).

Step 4: from here I believe I can make a continuous form that handles the date differences, for total duration, I think.

If you guys have any suggestions, or hopefully a different method of getting the field_datetime into a query/table with fields: field_datetime (clock-in times), field_datetime (clock-out times), please don't hesitate to suggest. Thanks!

Edit: to generate the 1,1,2,2,3,3... Or the 0,1,0,1. I made use of VBA function with static variables.

[SodaPop]

(I'm on mobile, so can't format my replies nicely, sorry)

@SodaPop
That's a nice different way of approaching it, but it has one flaw. It doesn't allow the employee to forget to clock-in/out, or sometimes clock-in twice (which happens often).

This is hardly unique as binary clocking is a pretty standard solve amongst professional clocking apps as the base clock method.

It isn't supposed to handle clock errors, as you pointed out you have a method to make corrections.

The problem here is design but also something we can't fix it seems so be that what it is you need a solution for where you are:

Assuming the clocks are all always correct and always have equal rows (which is what I think you are saying) you're headed in the right direction.

You said you have two queries so just number the rows for each query unique by name and date. So Q1 has John Doe's in clocks 1,2,3,4,5 and Q2 clocks 1,2,3,4,5 of say 01/05. Join those two queries by name (or other employee ID), date, and clock number. Pull the employee info you want and q1 clocks as in, and q2 clocks as out, right next to each other, then do the math.

Theory being you should have = in and out clocks so the rank of each oldest to newest should be the opposite sister record. Q1 R1 is the in punch to Q2 R1 out punch.

Hope that helps. Happy coding.

[ssanfu]

(I'm on mobile, so can't format my replies nicely, sorry)

@ssanfu
3) The duration(s) will be exported to an excel spreadsheet, where it is further manipulated. For this case, a "duration" of 01:30AM is sufficient,

So you must be saving it in the table "tbl_temp"?


OK, one more time....

TIME is not a DURATION!!

You cannot say "I bicycled from home to the store and back. It took me 1:30AM".
You would/can say "I bicycled from home to the store and back. It took me 1 hr and 30 minutes" or "I bicycled from home to the store and back. It took me 1.5 hrs".

Or maybe the hours you worked on 1/4/2018 is 8:00 AM???? Close enough.... right??

It doesn't matter WHAT you are doing in Excel, storing a DURATION as a TIME **WILL** bite you in the backside!!!

With regards to your calculations, I could modify my calculation to account for the hours as such:
(-[record 1]+[record 2] - [record 3] + [record 4] + .....)*24
(assuming even amount of records, which can be assumed in this case, as it is edited manually to ensure it is)

NO! You should be using the function DATEDIFF().




To quote the legendary MVP John Vinson:

"Access stores Date/Time fields as floating point numbers, counts of
days and fractions of a day (times) since midnight, December 30, 1899.


So, the numbers to the left of the decimal is the number of days since 12/30/1899.
The numbers to the right of the decimal are the fractional part of a day.




Good luck with your project........ Hope the employees don't riot.