Removing duplicate data

[DC27]

Using the query wizard, it's easy to set up a query to show all duplicate data.

But how can i remove 1 instance of the duplicate?

there's no restrictions on the data table

 

[Dennisk]

if there are not too many, then delete by hand.

 

[DC27]

thousands unfortuntely......

Any other ideas?

 

[Mike375]

Are the records complete duplicates or just duplicated in a field.

I am assuming that if there were say 4 duplicates you only want to remove one as opposed to the normal situation of removing 3 of them to leave the one record.

 

[DC27]

complete duplicates.

there's normally two copies in total, sometimes three.

I want 1 record remaining

 

[Mike375]

complete duplicates.

there's normally two copies in total, sometimes three.

I want 1 record remaining

That is simple enough.

1) Make a copy of your table and delete all the records from the copy.

2) Set the field properties for one of the duplicate fields to not accept duplicates. You go to the bottom of the table design page and you will see Indexed. Select Yes No Dupilates

3) Make an Append query to append the original table to the copy and all the duplicates will be knocked out.

 

[neileg]

Or use a select distinct query.

 

[Thinh]

Create a Make table query with a group by on all the fields and you should get a new table with non duplicate. when you think you have it all replace the old table with the new table create from the query.

 

[gemma-the-husky]

just one thing - is there any unique identifier in the table

ie are any of these records linked to records in other tables - because unless you do this by hand you will have NO control over which records (out of any duplicates) are deleted, and if one is deleted that links to naother table, this will cause you further problems

this may not be an issue, though

 

[Mike375]

just one thing - is there any unique identifier in the table

ie are any of these records linked to records in other tables - because unless you do this by hand you will have NO control over which records (out of any duplicates) are deleted, and if one is deleted that links to naother table, this will cause you further problems

this may not be an issue, though

He said earlier that they were complete duplicates

 

[gemma-the-husky]

complete doesnt necessarily mean complete though does it?

there may be some non-identical fields

 

[MSAccessRookie]

He said earlier that they were complete duplicates

I seem to have read this differently than you have, (perhaps like Gemma did?).

The Op BELIEVES that they are complete duplicates, but if within the record there is a field used as a foreign key to another table, then the "complete duplicate" could be different after all, and relationships in the other table could be affected when records are removed.

 

[gemma-the-husky]

that's exactly what i thought

i think you need to be a bit circumspect before arbitrarily losing effectively random rows - you need to be absolutely sure you dont need the duplicates

 

[MSAccessRookie]

that's exactly what i thought

i think you need to be a bit circumspect before arbitrarily losing effectively random rows - you need to be absolutely sure you dont need the duplicates

Or perhaps the Op needs to select a single RowID from the list of the "Duplicate Rows", and modify any entries to the other table(s) to conform to a single entry, before removing any of the duplicates.

 

[Mike375]

That is why I asked the OP the question. I guess we need to wait for the OPs return

 

[gemma-the-husky]

but the OP said he had 1000's of rows, so its a big job to do manually