Deleting duplicate records

[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?

 

[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.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RemoveDuplicates.mdb" which illustrates how to do this. You
can download it for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=285

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[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.