Compare two rows of data

[astrom33]

Hello everyone,

I am really new to Access and this forum has been TREMENDOUS help for me. I have unfortunately been unable to figure out how to do the following:

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.

 

[WIS]

Hello everyone,

I am really new to Access and this forum has been TREMENDOUS help for me. I have unfortunately been unable to figure out how to do the following:

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.

A thought:

Put in a chk box which is ticked for lunch break and LunchStart, LunchEnd times. This could be handled on your entry form. When the lunch break is ticked, have an AfterUpdate event to make the LunchStart, LunchEnd ctrls visible - ie set them as Visible = No on the ppty sheet. Then all data is 1 record per employee. A bit of arithmetic in the qry would achieve your objective.

 

[DCrake]

Because the times are relevant to the date you need to have at least 6 fields in your record

EmpID
WorkDate
StartTime
LunchStart
LunchFinish
StopTime

You can either store these as actual times or as minutes past midnight. However this can become complicated if you have a night shift.

You can then use summations to calculate mins worked before lunch and mins worked after lunch. Then by adding these together you can check if they worked over 5 hours (300 mins)

 

[astrom33]

Thanks everyone. Unfortunately I am really new to access and will have to go and search how to actually do the things you both suggested.

By the way, I failed to mention that each entry is catergorized by "subtype_code". If the "shift" is a work shift the subtype code column has a "W". If it is a lunch break "shift" then the subtype code is "N."

Please see attached example sheet.

Again, thanks for all your help and I will try and figure out how to do the suggestions above.

 

[astrom33]

I guess basically the formula I am looking for would look somewhat like this:

"If([hours]>=5...." the next part is what has me stumped. I want it to say if [hours] is greater than or equal to five and there is no lunch (i.e. "N" subtype code) on the same day then there was a meal period violation because the employee was entitled to a meal break on that day.