Query to find the most recent record for each duplicate record

[Microsoft News Server]

I am working with a customer database which, unfortunately, contains some
duplicate information. Existing customers were sometimes added as new
customers. I need to create a query which will find only the most recent
record of each group of records which contain the same customer name. Any
help would be appreciated. Here is an example of what I need:



Existing Data

Name Date Other Information

Jane Smith 3/21/2004 Address, Phone, etc.

Jane Smith 7/02/2006 Address, Phone, etc.

Jane Smith 9/27/2007 Address, Phone, etc.

Jane Smith 3/19/2008 Address, Phone, etc.

John Doe 1/21/2006 Address, Phone, etc.

John Doe 3/24/2007 Address, Phone, etc.

John Doe 4/1/2008 Address, Phone, etc.



Desired Query Output

Name Date Other Information

Jane Smith 3/19/2008 Address, Phone, etc.

John Doe 4/1/2008 Address, Phone, etc.

 

[John W. Vinson]

I am working with a customer database which, unfortunately, contains some
duplicate information. Existing customers were sometimes added as new
customers. I need to create a query which will find only the most recent
record of each group of records which contain the same customer name. Any
help would be appreciated. Here is an example of what I need:



Existing Data

Name Date Other Information

Jane Smith 3/21/2004 Address, Phone, etc.

Jane Smith 7/02/2006 Address, Phone, etc.

Jane Smith 9/27/2007 Address, Phone, etc.

Jane Smith 3/19/2008 Address, Phone, etc.

John Doe 1/21/2006 Address, Phone, etc.

John Doe 3/24/2007 Address, Phone, etc.

John Doe 4/1/2008 Address, Phone, etc.

A Subquery will do this for you:

Select [Name], [Date], [Other information] FROM yourtable
WHERE [Date] =
(SELECT Max([Date] FROM yourtable AS X WHERE X.[Name] = [yourtable].[name])

Of course, it's quite possible that you have two customers with the same name
- I'm John Vinson, and when I was in school there was a Professor John Vinson
in the med school (he got one of my paychecks, alas I never got his). Also I
hope your example is hypothetical; both Name and Date are reserved words and
should not be used as fieldnames.

 

[Eddie Stevens]

Thanks for the help, I think I have a general understanding of the query
that you came up with but I am having some trouble with the syntax. Here are
the actual table, query, and field names along with what I typed in for the
query. I am getting a syntax error.



SELECT Customers.ID, Customers.FullName, Customers.SaleDate FROM Customers

WHERE [SaleDate] = (SELECT Max([SaleDate] FROM Customers AS X WHERE
X.[FullName] = [Customers].[FullName]));



Existing Data: Table Name "Customers"

ID FullName SaleDate

12 Jane Smith 3/21/2004

15 Jane Smith 7/02/2006

17 Jane Smith 9/27/2007

25 Jane Smith 3/19/2008

45 John Doe 1/21/2006

79 John Doe 3/24/2007

89 John Doe 4/1/2008



Desired Query Output: Query Name "Current"

ID FullName SaleDate

25 Jane Smith 3/19/2008

89 John Doe 4/1/2008

 

[John Spencer]

SELECT Customers.ID, Customers.FullName, Customers.SaleDate
FROM Customers
WHERE [SaleDate] =
(SELECT Max([SaleDate])
FROM Customers AS X
WHERE X.[FullName] = [Customers].[FullName])

Parens are your friend and your traitorous enemy.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================