asked on

determine if employee is on time

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:

tblClockTime
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
tblEmployeeSchedule
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!

mbizup

Try this -

SELECT t.[Employee ID] AS EmpID,   t.Weekday, t.[In Sched] AS SchedArrTime, q.ClockDate, q.EmpInTime, iif(q.EmpInTime > t.[In Sched], 'LATE', 'ON TIME') AS [Arrived On Time]
FROM tblClockTime t
INNER JOIN 
(SELECT empID, MIN([In time]) AS EmpInTime, ClockDate, Weekday    
FROM tblEmployeeSchedule
GROUP BY empID, ClockDate, Weekday) q
ON q.empID = t.EmpID AND q.WeekDay = t.WeekDay

Open in new window

peter57r

The starting point is to get the first in and last out times for each employee.
Assuming that the Clock date and the in and out time fields are all date/time dadatypes then this is a simple query...

Select [Employee ID],[ClockDate] , [WeekDay], min([In time]) as TimeIN, max([Out time]) as TimeOUT
From tblClockTime
Group By [Employee ID],[ClockDate] , [WeekDay]

Save this query and you can then build a new query from this and the tblEmployeeSchedule table, joining on both emp Id and Weekday which compares the TimeIn with the scheduled time in.

colin911

ASKER

mbizup, thank you for your code but I could not get it to work. I am a little bit confused since the first select line selects from multiple tables...

Peter57r, this is what i need, but can you help me with the JOIN part? I thought this would be easy for me but I overestimated my ability....

peter57r

The query posted by Miriam does pretty well everything I described. It doesn't include the Max of the Time out but it should produce a valid output if the source field names and table names are correct.

To build this in the way I was describing, and assuming you have save the GroupBy query as a named query, then in the query grid of a new query add the named query and the tblEmployeeSchedule table.
Join the tables by dragging the EmployeeID field from the named query and drop it on the Employeeid field in tblEmployeeSchedule and then do the same for the Weekday fields.

You can then check where you are going by selecting all fields from both tables and viewing the result.

I don't really know how you want to present the 'late/ontime' comparison.
But one option would be add a new column ...

Status:iif([timein]>[In Sched],"Late", "OnTime")

mbizup

<<
I am a little bit confused since the first select line selects from multiple tables
>>

Take a look at the inner query... that is finding the earliest time that an employee has checked in on any given date.

The outer query joins this with the tblEmployeeSchedule table -- which lets you compare the employee's earliest time in on any given date with the "scheduled" time in.

This is effectively what Pete has suggested as well... although you may find it easier to use his method of creating the two queries separately instead of using a single query.

<< I could not get it to work. >>

If you post more of an explanation (error message, incorrect results, etc), I might be able to help you make it work (simply saying 'does not work' does not help pinpoint the issue)

colin911

ASKER

Mbizup,

After I enter your query and try to go to table view, I get asked to "Enter Parameter Value? empID" and then for every other field so I guess I am doing something wrong?

I note that there are the letters "t" and "q" in the statement. Am I supposed to put in some value for those?

Sorry I'm a newbie, but i really appreciate the help! :-)

colin911

ASKER

I am attaching the whole DB it's very small at this point since I am just at the beginning.

How do I compare the Scheduled time with the actual clock time in the query?
EmployeeDatabase.accdb

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

<<
I note that there are the letters "t" and "q" in the statement. Am I supposed to put in some value for those?
>>

Regarding the q and t - those are simply 'aliases' or nicknames...

q refers to the subquery
t refers to tblEmployeeSchedule

colin911

ASKER

mbizup,

Thank you very much for taking the time to help me here. You have a very thorough answer.

I have to import the clock times from another program, and that program gives the employees unique IDs (the CSid field), so was hoping to use those IDs as the main ID for the employee, but I guess it does not matter that much.

I see what you mean about the data types. I will work on fixing that. the tblClockTime does have text fields for the in/out times.

The Weekday is a number field in the tblEmployeeSchedule because that field does a lookup to the tblWeekday.