CTValue 1 14224 2 14224 3 14224

[John1Chr]

CTValue
1 14224
2 14224
3 14224
4 14224
5 14224
6 14225....

CFATTRIB
1 PROG
2 PROG
3 ACT
4 LLC
5 APT


CFATTVAL
1 1010
2 1020
3 101010
4 4
5 22

EFFDT
1 3/1/2004
2 3/2/2004
3 3/5/2004
4 3/1/2004
5 2/1/2005


I want the CFATTVAL value for the max effect date for CFATTRIB PROG for the CTVALUE (ie.1020 for 14224.) This should be simple, right? I can't seem to get all of the CTValues

(Select Max(EFFDT) From [Table1] M WHERE M.CTValue = [Table1]![CTVALUE] ) is where I'm at...not working.

Sorry - had trouble trying to post as a table. all the 1's are the first record in the fields of table1.

 

[John1Chr]

This works not sure if it's best though -

SELECT t.CTValue
, t.CFATTRIB
, t.CFATTVAL
, t.EffDt
FROM ( SELECT CTValue
, CFATTRIB
, MAX(EffDt) AS latest
FROM Table1
GROUP
BY CTValue
, CFATTRIB) AS m
INNER
JOIN Table1 AS t
ON t.CTValue = m.CTValue
AND t.CFATTRIB = m.CFATTRIB
AND t.EffDt = m.latest