Compare two rows of data

**Webtekr** · 06-01-2009, 07:23 AM

Hi everybody,

I have a database that shows when an employee clocks in and out for work. In my database each set of clock in and clock out is on individual rows. So for example if the employee clocks in at 9 and out at 5 the database displays it on the time in and out on one row. If, on the same day, the employee went to lunch from 12 to 12:30 it displays it on the next row as clock in at 12 and out at 12:30. Here obviously the lunch is considered a lunch shift where the employee is clocking in for lunch at 12 and out at 12:30.

I have been able to calculate the hours in each shift (row). However, now I want to have only the days when an employee worked more than 5 hours in one day and where he/she did not have a lunch break.

Any help would be GREATLY appreciated.

**mdbct** · 06-01-2009, 10:07 PM

I'm assuming you have a field that describes the shift - work vs lunch.
Create a query that does the summations you desire

Create a column that evaluates the Shift type. You can use something like:

Code:

Lunch:IIf([shiftType]="lunch",1,0)

Set the Total type to Sum
Set the criteria to 0

I've attached a small sample db.

**astrom33** · 08-13-2009, 07:49 PM

I need the same exact thing the poster needed. However, when I inputted new "work" shifts into the table that were less than 6 hours they still ended up on the query results. In other words, the sample database query does not filter out instances where an employee has a "work" shift that is shorter than 6 hours, but does not have a meal period. Under California law a meal period violation occurs when an employee works more than 6 hours long and does not get a 30 minute lunch break. So all the works shifts of 6 hours or less without a meal period would need to be filtered out because they do not constitute a lunch break violation under California law.

I am assuming, of course, that the original poster was looking for the same thing. In any case, that is what I am looking for.