Delete Duplicates

[Guest]

I am brand new to Access. I have a table that lists all of our clients. I
need to delete the duplicates. Not just the duplicates, but the duplicate
and the original. Is there a way to do this?

 

[Jeff Boyce]

Lauren

?Select the table row and hit the <Delete> button?

Are you really saying that you want to remove the original and the
duplicate(s)? What will you have left?

Take a look from the database window at the New Objects toolbar button. You
can select a new query, then use the wizard to build a "find duplicates"
query.

By the way, Access is really really dumb -- when you say "duplicates",
Access will be very literal. Do you consider the following to be
duplicates?:

John Smith, 1234 Elm Street
J. Smith, 1234 Elm St NE
J. J. Smith, 1243 Elm
John Smyth, 1234 Elm Ave

Access will NOT consider these duplicates.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Firstly you need some column, or combination of columns in the table in the
table, e.g. ClientID which identifies each client. You can then use the
DCount function in a DELETE query's WHERE clause to count the rows per client:

DELETE *
FROM Clients
WHERE DCount("*", "Clients", "ClientID = " & [ClientID]) > 1;

To do this in query design view just create a DELETE query on the Clients
table and enter DCount("*", "Clients", "ClientID = " & [ClientID]) in the
Field row of a blank column, uncheck the 'show' checkbox and enter >1 as the
critera for the column.

Ken Sheridan
Stafford, England

 

[Guest]

Jeff,

This is definitely what I need to do. I have clients with 2-4 different
accounts. I want to mail to clients that have account A but not Account B.
I've imported the list of clients with account A and B from Excel into one
table. I used the tax-id number as the unique identifier. I can get to a
point where a find duplicate query gives me the names of the duplicate
clients. But there are over 13,000 records in the table and 3000 duplicated
clients. To go through and manually delete them would take too much time.
Isn't there a command that can take the query results and remove them from
the table?

Thank you so much for your help!

Lauren

 

[John Spencer]

The SQL would look something like

DELETE DistinctRow [YourTable].*
FROM [YourTable]
WHERE [YourTable].[TaxID] IN (SELECT TaxId FROM [YourQuery])

OR
If the records in your query are updatable, then you could select 200
records (or so) in the query and press the delete key. Repeat 15 times and
your done.

OR
try opening your query in design view and then selecting Query: Delete
query from the menu. Then Select Query:Run from the menu.

 

[Guest]

Yes!!! This did it. The third option, delete query & run. Thank you so
much!!!

Lauren

The SQL would look something like

DELETE DistinctRow [YourTable].*
FROM [YourTable]
WHERE [YourTable].[TaxID] IN (SELECT TaxId FROM [YourQuery])

OR
If the records in your query are updatable, then you could select 200
records (or so) in the query and press the delete key. Repeat 15 times and
your done.

OR
try opening your query in design view and then selecting Query: Delete
query from the menu. Then Select Query:Run from the menu.

Jeff,

This is definitely what I need to do. I have clients with 2-4 different
accounts. I want to mail to clients that have account A but not Account
B.
I've imported the list of clients with account A and B from Excel into one
table. I used the tax-id number as the unique identifier. I can get to a
point where a find duplicate query gives me the names of the duplicate
clients. But there are over 13,000 records in the table and 3000
duplicated
clients. To go through and manually delete them would take too much time.
Isn't there a command that can take the query results and remove them from
the table?

Thank you so much for your help!

Lauren