Select most current set of records 1

[DanGriffin]

I am using Access 2002 and I am trying to create a query that selects the most current version of each code. Here is the data:

Code Title Wage Year
AC Account Clerk 1 2001
AL Advanced Lifeguard 9.56 2001
AM Assistant Manager 10.94 2001
BB Buddy Bear 1.00 2001
CA Cashier 7.6 2001
CO Courier 1.00 2001
EO Equipment Operator 1.00 2001
LI Lifeguard 9.12 2001
MA Manager 12.55 2001
TS Team Specialist 12.55 2001
AC Account Clerk 1.00 2005
AL Advanced Lifeguard 9.56 2005

I tried using MAX but it only returns the two 2005 records.

SELECT *
FROM Positions
WHERE Year IN
(SELECT MAX(Year) FROM Positions);

Any guidance would be greatly appreciated.

 

[PHV]

SELECT A.Code, A.Title, A.Wage, A.Year
FROM Positions AS A INNER JOIN (
SELECT Code, Max([Year]) AS MaxYear FROM Positions GROUP BY Code
) AS B ON A.Code = B.Code AND A.Year = B.MaxYear;

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

 

[DanGriffin]

Thanks PHV. I had no idea such a simple query would have such a complex solution!! I must have read 40 threads. All of them equally complex, far too complex for my simple need, I thought.

Thanks again.