CODEWITHSUNDEEP
sqlvbams-access
xxx
xxx

Reputation: 113

Finding multiple-row-duplicates in MS Access

I have a MS Access table where customer keys are matched with some interest rates of several years, one row for each year. Due to user interaction it can be the case that the exact same data of interest rates and years appears twice, but with different keys. I want to filter these in a way that

  [KEY] [year] [data1] [data2]
    1    2000    0,3     0,2
    1    2003    0,7     0,3
    1    2007    0,1     0,2
    2    2000    0,3     0,2
    2    2003    0,7     0,3
    2    2007    0,1     0,2
    2    2016    0,3     0,1

becomes

  [KEY] [year] [data1] [data2] 
    1    2000    0,3     0,2         
    1    2003    0,7     0,3    
    1    2007    0,1     0,2     
    1    2016    0,3     0,1

so if there exists historical correspondence one of the affected keys should be deleted (and of course this key should be updated in the customer table). Does anyone have an idea how to code this?

Upvotes: 1

Views: 36

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

DELETE t1.*
FROM yourTable t1
WHERE NOT EXISTS
(
    SELECT 1
    FROM 
    (
        SELECT [year], [data1], [data2], MIN([KEY]) AS KEY
        FROM yourTable
        GROUP BY [year], [data1], [data2]
    ) t2
    WHERE t1.[KEY]   = t2.[KEY]   AND
          t1.[year]  = t2.[year]  AND
          t1.[data1] = t2.[data1] AND
          t1.[data2] = t2.[data2]
)

Upvotes: 2

Related Questions