Access --- Remove duplicates based on Date range

Question

Good afternoon,

I cannot find any help for my specific situation online. I have a large table (roughly 1 million records) and I need to remove duplicates, BUT only the values that match within 42 days. Here are my specifics:

Fields: Company, Serial_Num, Week_Ending

I need to remove duplicates where the same Serial_Num was given to a company within 42 days. For example, if my data set is:

Company            Serial_Num            Week_Ending

ABC                    123456                  2/22/2014

ABC                    123456                  1/25/2014

I would want to delete the maximum Week_Ending row, so I would be left with the 1/25/2014 record.

If the Week_Ending date is greater than 42 days difference, then I would want both. For example:

Company            Serial_Num            Week_Ending

ABC                    123456                  2/22/2014

ABC                    123456                  1/4/2014

I would return both records. Is this possible to do? Thanks for any help/insight you can provide!

Answer 1

Off the top of my head, something like this:

DELETE *

FROM YourTable AS T1

WHERE EXISTS

    (SELECT *

     FROM YourTable AS T2

     WHERE T2.Company = T1.Company

     AND T2.SerialNum = T1.SerialNum

     AND T1.Week_Ending - T2.Week_Ending >= 1

     AND T1.Week_Ending - T2.Week_Ending <= 42);