Delete Duplicate Record

[cheer]

A table say call "A" consists of duplicate records as shown in the below attachment. Can anyone recommend to me on how to detect and delete duplicate records but I wish to leave ONE record ONLY at the end of deleting process.

Please take note, not all records are duplicating. You can refer to the below attachment for further understanding.

 

[MStef]

I suggest next.
Make a NewTable with the same fields as in this table.
Make a "Primary Key" on the "LotNo" field.
Make an "Append Query" with your table (entry in the query),
and a NewTable (out in the query). Run this query.
Before take a copy of your table.
After that you can to delete your table.

 

[cheer]

I suggest next.
Make a NewTable with the same fields as in this table.
Make a "Primary Key" on the "LotNo" field.
Make an "Append Query" with your table (entry in the query),
and a NewTable (out in the query). Run this query.
Before take a copy of your table.
After that you can to delete your table.

TQ. This is one way to resolve.

However, the suggestion can't help me as the system / database is not fully developed by me. Any other suggestion to archive unnecessary data but in order to maintain the existing table structure?

 

[CBrighton]

You can add an extra stage if you want the current table to be the one that ends up with the good data.

First create a temp table, then copy all records into there (dupes and uniques).

You can now run a delete query on the existing table to empty it.

After that you basically follow the instructions above, but instead of ruinning another make table query you are running an append query to append the unique records from the temp table back into the live table.


However, this will cause issues with autonumbers if you have anything linked on them (new record = new autonumber, regardless if it's the same data as a deleted record).