Select record with the most recent date

[CarolCook]

Hi:

I have a database that has an entry for a piece of equipment, the date it was moved to a new job and the new job. I need a query which shows where the equipment is now - i.e. the the equipment and the job line with the most recent date.

Select equipment, max(date),job
from equipment_list
group by equipment, job

doesn't work because the job is always different. I still get multiple lines per piece of equipment.

Thanks in advance,
Carol

 

[PHV]

Something like this ?
SELECT A.equipment, A.date, A.job
FROM equipment_list AS A INNER JOIN (
SELECT equipment, Max([date]) AS MaxDate FROM equipment_list GROUP BY equipment
) AS M ON A.equipment = M.equipment AND A.date = M.MaxDate

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

 

[CarolCook]

PH:
As always, your are the BEST. Thank you for your fast and accurate solution!
Carol