SQL Group BY Question

All Fields

[bgolfer66]

I want to select records from a table that contains an ID, Date, and let's say other columns, Something like this

ID DATE FIRST NAME LAST NAME
1 10/1/07 JOHN DOE
1 10/15/07 FRED BROWN
1 11/1/07 JOE BLOW
2 8/7/07 FRED SMITH
2 9/15/07 MARY JANE
3 9/15/07 JERRY SEINFELD

MY Query should return:

ID DATE FIRST NAME LAST NAME
1 11/1/07 JOE BLOW
2 9/15/07 MARY JANE
3 9/15/07 JERRY SEINFELD

one record per ID with the latest date.

HELP! I'm losing my mind... :-)

John

[phranque]

welcome to WebmasterWorld, bgolfer66!

try this:
select ID, MAX(DATE), FIRST NAME, LAST NAME from TABLE_NAME group by ID;

[bgolfer66]

That works for me in SQL Server, but alas, this is an Access problem.

Guess I should have stated that originally.

[phranque]

i'm no access expert but i would be surprised if access doesn't support standard sql syntax such as "group by".
(http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx#acfundsql_sqlinaccess)
what error message do you get?