Find Duplicates Query

[Matt]

I have a years worth of data where I'm trying to find records with duplicate
values in the "IndividualName" field. I am using the find duplicates query
in Access 2000. However, I only want records to return as duplicates if the
the "DateCreated" field are within 30 calendar days of each other. Does
anyone know how to do this? Any help is greatly appreciated. Thanks,

Matt.

 

[KARL DEWEY]

First create query to find duplecate names. Then use that query joined to
your table twice.
SELECT Matt.IndividualName
FROM Matt
GROUP BY Matt.IndividualName
HAVING (((Count(Matt.IndividualName))>1));

SELECT Matt_Dup_Names.IndividualName, Matt.DateCreated
FROM (Matt_Dup_Names INNER JOIN Matt ON Matt_Dup_Names.IndividualName =
Matt.IndividualName) INNER JOIN Matt AS Matt_1 ON
Matt_Dup_Names.IndividualName = Matt_1.IndividualName
WHERE (((Matt.DateCreated) Between [Matt_1].[DateCreated] And
[Matt_1].[DateCreated]+30 Or (Matt.DateCreated) Between
[Matt_1].[DateCreated] And [Matt_1].[DateCreated]-30) AND
((Matt_1.DateCreated)<[Matt].[DateCreated] Or
(Matt_1.DateCreated)>[Matt].[DateCreated]));

 

[John W. Vinson]

I have a years worth of data where I'm trying to find records with duplicate
values in the "IndividualName" field. I am using the find duplicates query
in Access 2000. However, I only want records to return as duplicates if the
the "DateCreated" field are within 30 calendar days of each other. Does
anyone know how to do this? Any help is greatly appreciated. Thanks,

Matt.

A "Self Join" query will do this:

SELECT <whatever fields you want to see>
FROM yourtable AS X
INNER JOIN yourtable AS Y
ON X.[IndvidualName] = Y.[IndividualName]
AND X.[primarykeyfield] < Y.[primarykeyfield]
WHERE Abs(DateDiff("d", X.[DateCreated], Y.[DateCreated]) < 30

The join clause on the primary key field (whatever that is in your table) is
to keep from finding each record as a duplicate of itself; using < rather than
<> ensures that you don't find A as a duplicate of B and also find B as a
duplicate of A.