Solved Remove Duplicates in access with Queries (1 Viewer)

fkcm95

New member
Local time
Today, 02:06
Joined
Feb 18, 2020
Messages
9
Hello,
i want to remove duplicates from the main table. I found a mini queries sql code in the internet, but they show me the count of the Duplicates.
How can I remove the duplicates and keep only the entry on the top(FirstID).

Code:
SELECT First(Tabelle1.Duplikate) AS [Duplikate Feld], Count(Tabelle1.Duplikate) AS AnzahlVonDuplikaten
FROM Tabelle1
GROUP BY Tabelle1.Duplikate;

Tabelle1

NumDuplicateTest
1​
a
1​
2​
b
2​
3​
b
2​
4​
c
4​
5​
c
5​
6​
c
6​
7​
d
7​
8​
d
8​
9​
d
9​


Thanks for the help
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:06
Joined
Feb 28, 2001
Messages
27,001
The Query Wizard has a pre-set that it would generate for you that would identify your duplicates. From there as a starting point, you could remove the duplicates.

However, I have a question. If you are removing duplicates, why does it matter which one you remove?
 

fkcm95

New member
Local time
Today, 02:06
Joined
Feb 18, 2020
Messages
9
Yes your right. It doesnt matter which entry I remove. I need in the final table one entry of this duplicate. You know what I mean? For example If I have three entries for car. Then I need one entry of car in my table and not three.

But how can I remove this? what do you mean ?
 

ebs17

Well-known member
Local time
Today, 02:06
Joined
Feb 7, 2020
Messages
1,882
SQL:
DELETE
FROM
   Tabelle1 AS T
WHERE
   EXISTS
      (
         SELECT
            NULL
         FROM
            Tabelle1 AS X
         WHERE
            X.Duplicate = T.Duplicate
               AND
            X.Num < T.Num
      )

Primarily, one would not even allow duplicates in data tables, for example by means of suitable clear indexing.

Eberhard
 
Last edited:

fkcm95

New member
Local time
Today, 02:06
Joined
Feb 18, 2020
Messages
9
Hi, in the solution from Eberhard, Access ask me a value for T.Duplicate and X.Duplicate? If I press a and a, i became the message that access would like delete all my entrys in the dataset/Table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:06
Joined
May 7, 2009
Messages
19,169
before you try, make a copy of the Table
you want to delete record from.
Code:
DELETE (SELECT COUNT(*) FROM Tabelle1 AS T1 WHERE (T1.Duplicate = Tabelle1.Duplicate) AND T1.Num <= Tabelle1.Num) AS Expr1, Tabelle1.*
FROM Tabelle1
WHERE ((((SELECT COUNT(*) FROM Tabelle1 AS T1 WHERE (T1.Duplicate = Tabelle1.Duplicate) AND T1.Num <= Tabelle1.Num))>1));
 

ebs17

Well-known member
Local time
Today, 02:06
Joined
Feb 7, 2020
Messages
1,882
Duplikate <> Duplicate
Your entry fee is contradictory.

Use the correct names as they appear from the table definition.

Eberhard
 

fkcm95

New member
Local time
Today, 02:06
Joined
Feb 18, 2020
Messages
9
before you try, make a copy of the Table
you want to delete record from.
Code:
DELETE (SELECT COUNT(*) FROM Tabelle1 AS T1 WHERE (T1.Duplicate = Tabelle1.Duplicate) AND T1.Num <= Tabelle1.Num) AS Expr1, Tabelle1.*
FROM Tabelle1
WHERE ((((SELECT COUNT(*) FROM Tabelle1 AS T1 WHERE (T1.Duplicate = Tabelle1.Duplicate) AND T1.Num <= Tabelle1.Num))>1));

This works fine with a database within 20 elements. But If I try this with 530000 elements, the query not run. But whyyy
 

ebs17

Well-known member
Local time
Today, 02:06
Joined
Feb 7, 2020
Messages
1,882
with 530000 elements
How do you get such a mass of erroneous data?
I say: avoid waste instead of removing it.

Of course, if you run a subquery per record in the table, it will take a little longer. If the subquery contains a little more than a simple comparison, then it does so much longer.

Strategies:
- Index usage enables performance
- Moving away from correlating subqueries, move the comparison to the FROM part

Eberhard
 

Users who are viewing this thread

Top Bottom