Remove Common Data In Access Table

Question

I'm using an Access Database and have the following question:

How is it possible to remove entire rows based on certain columns?

For example I have the following table:

Id | Name |   Desc  | Contact No. | 
1  | XYZ  | bla bla | 123456      | 
2  | ABC  | bla bla | 123456      |
3  | TUV  | hmmmmm  | 123456      |
4  | XYZ  | bla bla | 123456      |

I want to remove all lines of data which have duplicate information, so in this case, rows 1 & 4. The only issue of course, is the Id, which makes the overall row unique....

How would I go about removing this? Is there some SQL code that can handle this?

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

An autojoin for detecting the duplicate:

DELETE FROM Table
WHERE Id IN
(
    SELECT t1.Id
    FROM Table t1
        INNER JOIN join Table t2
            ON t1.Name = t2.Name
            AND t1.[Desc] = t2.[Desc]
            AND t1.Contact = t2.Contact 
)

If you want to keep only one row for each duplicate, look this question : Deleting duplicate rows from a table

DELETE FROM Table
WHERE Id NOT IN
(
    SELECT MIN(Id)
    FROM Table
    GROUP BY Name, [Desc], Contact
)

edited May 23 '17 at 11:50

Community

1
1

answered Apr 16 '13 at 12:55

Cyril Gandon

16,830
14
78
122

I have no access database under my hand. I know Access has a lot of limitations, but the two queries are pretty standard. – Cyril Gandon Apr 16 '13 at 13:08
Typing in this code causes access to complain "Syntax Error in Query Expression" - the query executes when removing the group by. – user559142 Apr 16 '13 at 13:34
using `GROUP BY [Name], [Desc], Contact` would fix that – Gord Thompson Apr 16 '13 at 14:19

fthiella · Answer 2 · 2013-04-16T14:01:22.540

2

If you need to delete all rows that have a duplicate, you could use this Ms-Access SQL query:

DELETE tbl.Id
FROM tbl
WHERE (((tbl.Id) In (SELECT tbl.Id
  FROM
    (SELECT [Name], [Desc], [Contact]
     FROM tbl
     GROUP BY [Name], [Desc], [Contact]
     HAVING COUNT(*)>1)  AS [t1]
  INNER JOIN tbl
  ON ([t1].Contact = tbl.Contact)
     AND ([t1].[Desc] = tbl.[Desc])
     AND ([t1].[Name] = tbl.[Name]))));

To keep just the first duplicate you could use this:

DELETE tbl.Id
FROM tbl
WHERE (tbl.Id) Not In (SELECT Min(tbl.Id) AS MinId
FROM tbl
GROUP BY tbl.[Name], tbl.[Desc], tbl.[Contact]);

edited Apr 16 '13 at 14:01

answered Apr 16 '13 at 13:06

fthiella

48,073
15
90
106

It complains when adding the Group By – user559142 Apr 16 '13 at 13:29
@user559142 wrong copy&paste i'm sorry...:) now it should be okay, it works on my database – fthiella Apr 16 '13 at 14:02

Remove Common Data In Access Table

2 Answers2