Electronic TimePro Timeclock Database Question

[JG]

I have inserted 2 lines of data from a TimePro timeclock database. The first
line reflects the 1st half of an employee's shift. The 2nd line reflects the
2nd half of the shift. The time between the 2 shifts equates to a meal
break.

Here's the question.... How would I write a query to determine the lenght
of the meal break. The purpose for this question is that I want to ensure
that everyone working more than 6 hours and takes a meal break takes at least
30 minutes (labor law).

Since the data is on two different lines within the database, this is where
I am having the difficulty. By the way, I am able to covert these time
stamps to just dates and time of day.


LastName FirstName PaidIn PaidOut
Alejandro James 5/16/2008 3:03:00 AM 5/16/2008 8:38:00 AM
Alejandro James 5/16/2008 9:38:00 AM 5/16/2008 11:10:00 AM

 

[KARL DEWEY]

This will work if the shift is not split over two days --
SELECT JG_Table.LastName, JG_Table.FirstName, JG_Table.PaidOut,
JG_Table_1.PaidIn,
Format(CVDate([JG_Table_1].[PaidIn]-[JG_Table_1].[PaidOut]),"h:nn") AS [Break
Period]
FROM JG_Table INNER JOIN JG_Table AS JG_Table_1 ON (JG_Table.FirstName =
JG_Table_1.FirstName) AND (JG_Table.LastName = JG_Table_1.LastName)
WHERE (((JG_Table.PaidOut)<[JG_Table_1].[PaidIn]) AND
((DateValue([JG_Table].[PaidOut]))=DateValue([JG_Table_1].[PaidOut])));

 

[Ron2006]

Not knowing your data or your system, don't forget to think about the
situations of multiple ins and out for the same day (doctor's
appointment in middle of afternoon) and/or the situation of going home
sick so only one clockin and one clock out (this one would probly not
even show up in your query because of only one record.)

In one system I have seen we receive multiple records with the
computed time for each in to out computed and on the record. (or it
could be computed.) We then compute total minutes from first clock in
to last clock out and then suptract from that the times for each of
the clockin to clock out records. The difference is then considered
lunch (but in reality could be lunch and doctor's appointment or
whatever.) I cannot think of any scheme that is fool proof because of
the potential of multiple ins and outs and allowable Dr. appointment,
etc.

Good Luck

Ron