Date difference duplicates query between last and penultimate dates

Question

I have a MS Access database that contains, among other things, a 'name' field and a 'date' field. The names contain duplicates because events to individuals with the 'name' sometimes occured more than once.

What I want to do is identify duplicate 'names' where where the difference between the last 'date' less that of the penultimate date is grater than 2 weeks. Otherwise, if the date difference is less than 2 week, the name will not be considered a duplciate.

I hope this makes sense.

Fionnuala Fionnuala · Accepted Answer · 2010-11-17T21:24:50

Do you mean:

SELECT  m.SName, m.SDate 
FROM
   (SELECT STable.SName, 
           STable.SDate, 
           (SELECT TOP 1 Sdate 
            FROM STable s 
            WHERE s.SName=STable.SName 
            AND s.SDate<STable.SDate 
            ORDER BY SDate DESC) AS PreviousDate, 
            [SDate]-Nz([PreviousDate],[SDate]) AS Diff
    FROM STable) m
WHERE Diff=0 or Diff>14

Date difference duplicates query between last and penultimate dates

1 Answers