determine if employee is on time

Posted on 2013-05-31
Last Modified: 2013-06-03
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!
Question by:colin911
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
LVL 61

Expert Comment

ID: 39210464
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
(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

LVL 77

Expert Comment

ID: 39210471
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.

Author Comment

ID: 39210650
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....
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 77

Expert Comment

ID: 39210793
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")
LVL 61

Expert Comment

ID: 39210815
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)

Author Comment

ID: 39211681

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!  :-)

Author Comment

ID: 39211978
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?
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 39212129
Okay... there are several issues here -- which have prevented that query from working as-is.

First, the table structure you described in this thread - field names, etc is quite different from your actual database.  I've corrected the field names in the query to account for this.

There are bigger problems though.

The table that logs the employees in/out times does not track these by employeeID as described in your initial posts... it uses first and last name AND there is a leading or trailing space in those names that is preventing any of the records in these tables from matching up even if you try matching based on name.

To properly normalize this table, you should be using EmployeeID.  I added an employeeID field to this table to make it more like what you posted, and ran an update query to populate that based on employees last and first names, trimming the extraneous spaces.

Additionally, the employees in/out times have a Text datatype in one table and a Date datatype in the other.  Both should use Date as the data type.

Finally, your weekday is a number in one table and text in the other (look at the table's design view, not the datasheet view to see this).  You should change this so that the Day field in both tables is either text or numeric (be consistent).

This query works, based on the data you have in your database and my added EmployeeID field:

SELECT t.[EmployeeID] AS EmpID, t.Weekday, t.[In Sched] AS [Employee Sceduled Arrivial Time], q.ClockDate, q.ActualArrivalTime, iif(CDate(q.ActualArrivalTime) > t.[In Sched], 'LATE', 'ON TIME') AS [Arrived On Time]
FROM tblEmployeeSchedule t INNER JOIN (SELECT empID, MIN([In time]) AS ActualArrivalTime, ClockDate, [Day] FROM tblClockTime GROUP BY empID, ClockDate, Day) q
ON q.EmpID = t.EmployeeID AND weekdayname(t.Weekday,True) =

Open in new window

However, it compensates for the discrepancy in datatypes between your tables (I didn't actually fix the Time and Day fields for you).  You should actually change your tables so that the data types match.  If you do this, the CDate and WeekDayName functions will no longer be needed to match the data.

See the sample:
LVL 61

Expert Comment

ID: 39212130
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

Author Comment

ID: 39215878

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.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Runtime Error '3070' 5 52
Access Form - Button Format Issue 13 51
access vba 5 58
What are the recommended security measures to put in place? 19 94
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question