Max Query

[Joshann]

I have exactly the same problem that kbreiss had in this post:
http://www.access-programmers.co.uk/forums/showthread.php?threadid=44723&highlight=max

However, neither Pat Hartman nor jfgambit's suggestions work. Here's kbreiss' problem again. You have this table:
PROJ_ID-----UPDATE_DATE-------WKLY_UPDATE
---87------------3/24/2003---------another test
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

How do you get a query that will show the most recent WKLY_UPDATE for each PROJ_ID, as in:
PROJ_ID-----UPDATE_DATE-------WKLY_UPDATE
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

Pat Hartman's suggestion yields:
PROJ_ID-----UPDATE_DATE-------WKLY_UPDATE
---87------------3/25/2003---------another test
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

jfgambit's suggestion appears to work right at first. You get:
PROJ_ID-----UPDATE_DATE-------WKLY_UPDATE
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

But if you change the values in the table to be like this:
PROJ_ID-----UPDATE_DATE-------WKLY_UPDATE
---87------------7/24/2003---------another test
---87------------3/25/2003---------this is a test
---90------------3/25/2003---------one more

You get:
---87------------7/24/2003---------this is a test
---90------------3/25/2003---------one more

But you should actually be getting:

---87------------7/24/2003---------another test
---90------------3/25/2003---------one more

Can anyone help with this? (Sorry for the long message.)
Thanks!

 

[namliam]

SELECT Table2.Proj_ID, Table2.Update_Date, Table2.Wkly_Update
FROM Table2 INNER JOIN (SELECT Table2.Proj_ID, Max(Table2.Update_Date) AS MaxOfUpdate_Date
FROM Table2
GROUP BY Table2.Proj_ID) as
Query2 ON Table2.Proj_ID = Query2.Proj_ID
WHERE (((Table2.Update_Date)=[MaxOfUpdate_Date]));

If your table would be named Table2, then above is a 1 query sollution to get what you want.

Regards

The Mailman

 

[Joshann]

That did it. Thank you soooo much! You have no idea how long I've been trying to figure this out!

 

[namliam]

4 minutes, where 2,5 minutes are creating and filling the table for testing...



Regards