Update Query based on Difference in time

  • Thread starter Thread starter twalsh
  • Start date Start date
T

twalsh

I have a table keeping track of attendance for employees. We use a point
system, so that if an employee is absent or late, points are deducted. How
can i add the points field to each record?? There is a 15 minute grace period
in this as well, i would want it to look something like below, where late
employees get -1 and abasent get -2. The Scheduled Start and Actual Start
fields are imported from other sources and are in the Date/Time format

ID EmployeeID Sch_start Actual_Start Points
1 12345 8:00 AM 8:19 AM -1
2 45678 9:00 AM 9:00 AM 0
3 78354 8:00 AM -2
4 84627 7:00 AM 7:15 AM 0
 
SELECT id, employeeID, sch_Start, actual_start,
dateDiff("n", sch_start, actual_start) AS deltaTime,
switch( deltaTime IS NULL, -2,
deltaTime<=15, 0,
true, -1)
FROM tableName



should do.
 
This is in SQL view of the Update Query?

Michel Walsh said:
SELECT id, employeeID, sch_Start, actual_start,
dateDiff("n", sch_start, actual_start) AS deltaTime,
switch( deltaTime IS NULL, -2,
deltaTime<=15, 0,
true, -1)
FROM tableName



should do.
Click to expand...
 
That would be in an SQL view. It does not update anything. Just use the
query, rather than the initial table, each time you need the computed
expression. You should avoid SAVING computed expressions in a table, if a
simple SELECT query can do the job.


Vanderghast, Access MVP
 
Back
Top