[CharlieMay]

To start off with. This is being done in Access and I'm not getting the results I expect.

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.

[thava]

may i know what is the field type of clock time,if it is datetime then use the max funtion or else we need to work a little bit
now i considered the clocktime as a datetime field

SELECT Type as LastType FROM TimeClock
WHERE EmpID = 35 and ClockDate = #7/16/2012#
 AND ClockTime(
SELECT max(ClockTime) FROM TimeClock
WHERE EmpID = 35 and ClockDate = #7/16/2012#
)

hopr this will solve your problem

[baavgai]

In the above you forgot an =. However, while you're at it, I'd prefer a join and to only type parameters once, so:

SELECT a.Type as LastType
	FROM TimeClock a
		INNER JOIN (
			SELECT EmpID, ClockDate, MAX(ClockTime) as ClockTime
				FROM TimeClock
				WHERE EmpID = 35 
					AND ClockDate = #7/16/2012#
				GROUP BY EmpID, ClockDate
			) b 
				ON a.EmpID=b.EmpID 
					AND a.ClockDate=b.ClockDate
					AND a.ClockTime=b.ClockTime

[CharlieMay]

thava, thanks. That works.

baavgai, Thanks, I saw the missing = when I read his code last night. Giving your example a try first gave a circular reference on the As ClockTime so I just renamed the aggregate field name. Then, it prompted for Enter Parameter Value b.ClockTime. I tried removing the last line and a result was produced but it just displayed all the ClockTypes for that specified date, and empID instead of the one with the greatest time. And then it hit me. I had to remove the last line because b.ClockTime no longer existed due to renaming it. So once I fixed that it worked.

Thanks guys. Both examples produce the result I needed. I really need to get a lot stronger with my sql. Time to purchase a book on the subject, what would you recommend for someone that want's to go beyond simple select statments and parsing the results to letting the query produce the exact result like this one?

[DarenR]

Are you trying to find the last (n) as in the last one created or the newest one created or the last numerical (n)?

[CharlieMay]

DarenR, Both examples above solved the problem. But it was finding the last chronological occurrence of a given employee and date regardless of where it appeared in the table. As the example above shows. From the table (taking the time out) it would appear that the operator clocked out and then clocked in and last(n) of the type would produce clockin. But with the time involved, it shows that the employee clocked in and then clocked out. I was just trying to find the last(n) of the type as it would appear chronologically.

[DarenR]

yeah, I see that now--- your previous post wasnt showing up on my end or I wouldn't have even bothered typing what I did. Glad you got it resolved.. Have a good one...........