Finding multiple-row-duplicates in MS Access

Question

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?

I think you have a typo in the last row of your desired output. Should the [KEY] for 2016 be 2 ? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 23, 2016 at 7:23
No, I want to delete one of the keys, so all the data should be key 1 or 2. The Background is that several rates are connected to one key, but if you update one of the rates (adding the data for 2016) and forget to agree to update the others, one extra key is produced that has the same data except the newest one. I want to write a program that reverses this process — xxx
– xxx, Commented Sep 23, 2016 at 7:47
In the example I just thought of the one with the lower number, but maybe it makes more sense to keep the one with the complete data — xxx
– xxx, Commented Sep 23, 2016 at 8:07
The query I gave below will retain the lower key for each given year. If you want something other than this, it probably would be possible as well. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 23, 2016 at 8:11

Tim Biegeleisen · Accepted Answer · 2016-09-23 09:37:25Z

2

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]
)

edited Sep 23, 2016 at 9:37

answered Sep 23, 2016 at 7:21

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xxx Over a year ago

Thanks for answering! This works fine for the upper example, but if I add another key 3 with years e.g. 2000 and 2003 with different data1 and data2, they will be deleted. So I can't go for Min(Key) as the relationship key-year can appear more than twice. Do you have an idea how to include that? Sorry for bad elaborating.

Collectives™ on Stack Overflow

Finding multiple-row-duplicates in MS Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related