Find Duplicates Query

[Matt]

I have a Find Duplicates Query based on the "Name Field". Some sample
results are below.

John Doe 12/3/2007
John Doe 12/15/2007
John Doe 1/28/2008

Is there a way to further restrict results to only return results that the
date fields are within 30 days of other? So in the above example, the bottom
result would not be returned because it is not within 30 days of any other
results.

Any help is greatly appreciated.

Thanks,

Matt.

 

[Allen Browne]

Use a subquery to identify whether there is another record within 30 days.

This kind of thing:

SELECT ID, [TheName], [TheDate]
FROM [Table1]
WHERE EXISTS
(SELECT Min(TheDate) AS TheOtherDate
FROM Table1 AS Dupe
WHERE Dupe.TheName = Table1.TheName
AND Dupe.ID <> Table1.ID
AND Abs(DateDiff("d", Dupe.TheDate, Table1.TheDate)) <=30);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord