CODEWITHSUNDEEP
sqlms-access
Kaw4Life
Kaw4Life

Reputation: 209

Access 2010 Find Most Recent Status via SQL

Need list of "ALL" document numbers (k002) with their the most recent maintenance date (lm01_s) and status_code. The code below finds last date from the entire table and any record with that date. This is not what I need. There is only one table. If I drop the status_code from the equation, this is easy.

SELECT k002, lm01_s, status_code
FROM stat_trans
WHERE (lm01_s = ANY (SELECT MAX(lm01_s) FROM stat_trans)) ORDER BY lm01_s;

I have also tried this ...

SELECT k002, lm01_s, advice_code
FROM romis_stat_trans
WHERE lm01_s IN (((SELECT Max(lm01_s) FROM romis_stat_trans GROUP BY k002)));

I have tried so many things that I forget what I have tried. Everything has been a dead end.

Upvotes: 1

Views: 71

Answers (1)

Mike
Mike

Reputation: 620

Use a subquery in the where clause to return only the records where lm01_s is equal to the max lm01_s. I found it's important to use a table alias or else Access will confuse the fields.

select k002, 
       lm01_s, 
       status_code
from stat_trans
where lm01_s=(select max(sc.lm01_s)
              from stat_trans as sc
              where sc.k002 = stat_trans.k002)

Upvotes: 1

Related Questions