My data is
RecordID EmpID Type ClockTime ClockDate 1 35 CLOCKOUT 2:55 PM 7/16/2012 2 35 CLOCKIN 1:55 PM 7/16/2012
As you can see they are backwards in the database because an employee forgot to clockin and it was entered by the administrator putting it in the next available row.
What I'm trying to do is find the last type performed on the given date by the operator. Of course a simple
SELECT Last(Type) as LastType FROM TimeClock WHERE EmployeeID = 35 And ClockDate = #7/16/2012#
yields CLOCKIN but the employee has actually clocked out and that should be the last type found. So I figured I needed to order the data.
Since adding time so that I can order it would produce both rows except in chronological order I needed to select from the result of that select statement. I thought this would work.
SELECT Last(Type) as LastType FROM ( SELECT Type, ClockTime FROM TimeClock WHERE EmpID = 35 and ClockDate = #7/16/2012# ORDER BY ClockTime)
Again though this produced CLOCKIN as the last(n) record.
Am I going about this wrong? I was under the impression that the Select inside the FROM that has CLOCKOUT as the last result would be the result of the Last(ClockType) in the sorted result.