duplicates within fields

[Tuxla]

Hi
I have a table with the following fields:
Surname
Address
Phone number
Date

I want to create a query that shows the latest entry for a particular
surname, so if I have the following in the table:

Smith, 1 New Street, 01234567, 01/06/2008
Smith, 5 Old Road, 76543210, 01/07/2008
Jones, 14 The Avenue, 98765432, 01/07/2008

I would like it to bring through only:
Smith, 5 Old Road, 76543210, 01/07/2008
Jones, 14 The Avenue, 98765432, 01/07/2008
and to ignore the first reference to Smith

Is this possible, please?
I've tried using Max in the date field, but it doesn't see both Smiths as
the same because the address and phone number are different and pulls them
both through.

Any help would be greatly received. Oh, and I'm quite new to Access so it
needs to be in fairly simple terms, please

Thanks

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Hopefully "Date" is just an example, and your field name is not really this
reserved word.

 

[Tuxla]

that's great, thank you so much