Finding the Greatest date value.

[Albion]

I have a table

tblRates
--------
Item_NUM = key
Employee_NUM = Link back to tblEmployees
RATE = Hourly rate.
TIMESTAMP = timestamp when the item was added

This table includes all rate change for each employee. I want to return the record from each unique [Employee_NUM] with the most recent [timestamp].

tblRates might have these 7 records ([Item_NUM], [Employee_NUM], [Rate], [timestamp])

1, 107, $15, 9/1/2009
2, 107, $16, 9/2/2009
3, 107, $18, 9/3/2009
4, 108, $12, 8/15/2009
5, 108, $10.50, 9/1/2009
6, 109, $21, 9/1/2009
7, 109, $20, 9/2/2009

I want my query to return one entry for each employee with that employees most recent rate change.

3, 107, $18, 9/3/2009
5, 108, $10.50, 9/1/2009
7, 109, $20, 9/2/2009

Previously I made the mistake of just using =DMAX("[RATE]", "tblRates", "[Employee_NUM] = " & Item_NUM") in the Text box on the report I'm printing. This worked great as long as all employees always received an increase. With the recession many employees received a decrease. My DMAX formula still returns the MAX rate which isn't the most recent rate. I tried entering =DLOOKUP("[RATE]", "tblRates", "[Employee_NUM] = " & Item_NUM & " AND " & MAX(timestamp)) but that didn't work. I then thought instead of using a formula in the report that I'd just create a query to find what I explained above and print the report from that query alone. Unfortunately I am not able to figure it out and hoped someone here might help me.

Thanks

-Craig

 

[dhookom]

Try:

SELECT [Item_NUM], [Employee_NUM], [Rate], [timestamp]
FROM tblRates
WHERE TimeStamp = 
(SELECT MAX(TimeStamp) 
 FROM tblRates R
 WHERE R.Employee_Num = tblRates.Employee_Num)

Duane
Hook'D on Access
MS Access MVP