determine if employee is on time
Posted on 2013-05-31
I'd like to use access to determine if an employee is on time but having a hard time finding a simple way to compare the data to return the values I need. Appreciate any help I can get. To do this I have two sets of data.
1. I have the employee clock times that looks like this and comes imported from an excel source:
ClockDate Weekday Employee ID In time Out time
05/20/2013 Mon 123 8:32 AM 9:31 AM
05/20/2013 Mon 123 9:55 AM 1:17 PM
05/20/2013 Mon 124 6:40 AM 1:05 PM
05/20/2013 Mon 124 2:59 PM 7:02 PM
05/21/2013 Tue 123 8:35 AM 4:02 PM
05/21/2013 Tue 124 9:05 AM 10:02 AM
05/21/2013 Tue 124 11:18 AM 5:01 PM
05/21/2013 Tue 125 6:10 PM 8:01 PM
05/21/2013 Tue 125 8:45 PM 10:01 PM
2. I have the employee schedule that looks like this
Employee ID Weekday In Sched
123 Mon 8:30 AM
123 Tue 8:45 AM
124 Mon 6:45 AM
124 Tue 8:30 AM
125 Mon 6:00 PM
125 Tue 6:00 PM
IF there was only one clock in and out for the day, then it would be easy for me to compare both tables with a query to see if the employee was late and by how much. But since tblClockTime has several clock times for the employee for the day, how can I tell access to only compare the first clock time for the day (and maybe in the future I may want to see if they leave early).
Appreciate your help!