Deleting duplicate records

R

RobM

I populated a table using an Append query.
I created a duplicate records query using the Wizard. There are 20000
records.
Now I need to delete the duplicates out of this query.
How do I do that?
 
G

ghetto_banjo

this may be a duplicate post, im not seeing my first one.

anyways, instead of deleting those duplicate records, lets just grab
all the records that are not duplicates. (an unmatched query).


do a LEFT JOIN from your main table to your duplicate table/query on
primary keys. Choose your primary key from the DUPLICATE table, and
set the criteria to Is Null.


This will pull ALL Records in the main table that are NOT in the
duplicate table/query.
 
R

Roger Carlson

D

Duane Hookom

FIRST: BACKUP YOUR MDB

Every table should have a primary key. If your's doesn't add an autonumber
field named "PK" and make it the primary key.

Then make a totals query the groups by the fields you define as being
duplicates. Add the PK field to the query grid twice, once using Count and
the other as Max as the Total.

Change this query into a maketable query and make the table
"tblRecordsToDelete". It might look something like:

SELECT Max(PK) as MaxPK INTO tblRecordsToDelete
FROM tblYourtable
GROUP BY FldA, FldB, FldC, FldD,...
HAVING Count(PK) = 2;

Then open tblRecordsToDelete in design view and set MaxPK as the Primary key.

You can then join tblRecordsToDelete to your original table and delete the
records.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top