Using MAX ?

[rrhandle]

I have a table with the fields EmployeeID, HourlyRate, and ReviewDate. Each time an employee receives a raise, a new record is created. Now I want to query the table to find the most recent HourlyRate for each Employee. I can get the MAX ReviewDate per employee (i.e. the latest date they received a raise), and I can return their EmployeeID, but I cannot return the HourlyRate for that date. Can this be done in one query?

 

[dhookom]

I would add an ID primary key autonumber field to [table with the fields] and then change the name of the table to something like "tblEmpHrRateHist".

SELECT *
FROM tblEmpHrRateHist
WHERE ID = 
  (SELECT TOP 1 ID
   FROM tblEmpHrRateHist h
   WHERE tblEmpHrRateHist.EmployeeID = h.EmployeeID
   ORDER BY ReviewDate DESC);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way:
SELECT A.EmployeeID, A.HourlyRate, A.ReviewDate
FROM yourTable As A INNER JOIN (
SELECT EmployeeID, Max(ReviewDate) As LastDate FROM yourTable GROUP BY EmployeeID
) As L ON A.EmployeeID = L.EmployeeID AND A.ReviewDate = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rrhandle]

Thanks, PHV! Works great.