Show Latest Record by Date

[Elvis72]

I have searched and searched through the posts and cannot find a simple answer to this problem.

I just need to take this:

Request Date Proposal Number Request Number
12/1/2006 23063 1
1/15/2007 23063 2
2/15/2008 60001 1
3/15/2008 60001 2

And make it:

Request Date Proposal Number Request Number
1/15/2007 23063 2
3/15/2008 60001 2

Any help is GREATLY appreciated!~

 

[lespaul]

SELECT Max([Request Date]), [Proposal Number], [Request Number] From TableName
GROUP BY [Proposal Number], [Request Number]

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Elvis72]

Thanks for the help, when I input this:

SELECT Max([Request Date]), [Proposal Number], [Request Number] From Budget Adjustment Table
GROUP BY [Proposal Number], [Request Number]

I get a Syntax error in FROM clause?

Thanks!~

 

[dhookom]

You are specifying 3 tables or 2 tables and 1 alias or 1 table and 2 aliases because your table name contains spaces. If you have spaces, you must wrap your table (or other object names) in []s.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[r937]

leslie, your query will return the same rows as in the given sample rows

if you are going to use GROUP BY, then you have to leave [Request Number] out, and bring it back into an outer query, like this...

SELECT g.maxdate as [Request Date]
     , t.[Proposal Number]
     , t.[Request Number]
  FROM [Budget Adjustment Table] AS t
INNER
  JOIN (
       SELECT Max([Request Date]) as maxdate
            , [Proposal Number]
         FROM [Budget Adjustment Table]
       GROUP 
           BY [Proposal Number]
       ) AS g
    ON g.[Proposal Number] = t.[Proposal Number]

alternatively, use a correlated subquery, like this...

SELECT [Request Date]
     , [Proposal Number]
     , [Request Number]
  FROM [Budget Adjustment Table] AS t
 WHERE [Request Number] = 
       ( SELECT Max([Request Date])
           FROM [Budget Adjustment Table]
          WHERE [Proposal Number] = t.[Proposal Number] )

r937.com | rudy.ca

 

[lespaul]

good catch...

 

[PHV]

Rudy:
ON g.[Proposal Number] = t.[Proposal Number]
[!]AND g.maxdate = t.[Request Date][/!]

 

[r937]

good catch...



r937.com | rudy.ca

 

[lespaul]

lol, some days are just like that huh!

les