Delete duplicates (2000)

Topic

New Reply

Somehow I was assigned to help a group with their database.

They have managed to get quadruplicate records in their table.

I don’t know how & I don’t care how.

Anyone have an easy way to remove the extra records while keeping the first record?

Thanks!

Reply | Quote

Replies

Well, if the duplicate records are missing info, then filter for those records missing info, highlight, and delete. Otherwise,in the database window click on the table, copy, paste (When you paste, paste “structure only”. Now go into your new table in design view, and assign the duplicated field (hopefully some sort of order number or unique name) as a primary key.

Next, create an append query. Choose your newly created table as the table to append to, select the table with duplicates as the data source, and drag all the fields onto the query design grid. Click run, and then click “yes” twice.

PS – if all of the records contain different info, and you wish to save only the first one created, let us know. That gets slightly more complicated, though still easy to do

Reply | Quote

Some other possibilities:

If the records are complete duplicates, you can create a query based on the table with the Unique Records property set to Yes. Change it to a make-table query to create a new table with unique records.

Use the Find Duplicates Query Wizard to create a query that identifies the duplicate records. If the number isn’t too large, you can remove the extras manually.

Reply | Quote

Hans,

I tried your idea thinking it would work.

Even though the rows look alike, they are all being returned in the query…..

Reply | Quote

I had to make Unique Values true, not Unique Records….Now it is working!

Reply | Quote

Glad you found it. That Unique Records didn’t work means that at least one field in the record is different (perhaps an AutoNumber field). Unique Records acts on entire records, Unique Values operates only on fields that are displayed in the query.

Reply | Quote