Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show Latest Record by Date

Status
Not open for further replies.

Elvis72

Technical User
Joined
Dec 6, 2007
Messages
211
Location
US
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!~
 
Code:
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
 
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!~
 
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
 
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...
Code:
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...
Code:
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
 
Rudy:
ON g.[Proposal Number] = t.[Proposal Number]
[!]AND g.maxdate = t.[Request Date][/!]
 
lol, some days are just like that huh!

les
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top