Delete Duplicate Records MsAccess 2003 (1 Viewer)

[access2010]

Hello.
I am able to group the Group the Duplicate Records in our database, but I am hitting my head against the wall in trying to delete any duplicate Stock Names or Stock Codes.
Could I please be advised as to what I am doing wrong?
Thank you.
Crystal

 

[theDBguy]

Hi Crystal. Sorry I can't download your file right now, but does your table have a primary key? If so, you can group the records and select the Min ID and then use the result in a DELETE query to remove the duplicates.

Sent from phone...

 

[access2010]

Thank you theDBguy for your note.
The MsAccess 2003 Database that we are using is over 10 years old and has LOTS of Duplicate Stock Names and Duplicate Stock Codes.
The data base when it was set up did not have any Primary keys.
I can not add a Primary Key or Index the fields until the Duplicate records have been removed.
Your suggestion on fixing this problem will be appreciated.
Thank you
Crystal

 

[theDBguy]

Thank you theDBguy for your note.
The MsAccess 2003 Database that we are using is over 10 years old and has LOTS of Duplicate Stock Names and Duplicate Stock Codes.
The data base when it was set up did not have any Primary keys.
I can not add a Primary Key or Index the fields until the Duplicate records have been removed.
Your suggestion on fixing this problem will be appreciated.
Thank you
Crystal

Okay, the next possible approach is to copy the table but copy the structure only. Then set up the duplicate fields as a unique index or a primary key. Then, append the old table data into the new table. It should result in all duplicates removed. You can then decide to replace the old table with it or delete the old data and replace them with the unique data from the new table.

 

[access2010]

Thank you for your suggestion and this was the FIRT Append Query that I have made, BUT I must have made an error.
Attached are screen shots of what I have done and the problem encountered.
Nicole

 

[arnelgp]

see Query1.

 

[Pat Hartman]

If you are going to make Stock Code the PK, you should get rid of the autonumber. If you use an autonumber in a table, it should be the PK. BUT, what are you currently using as a Foreign key? If it is the existing autonumber, you have other cleanup to do before you can complete this duplicate elimination project.

 

[access2010]

see Query1.

I am smelling success, arnelgp.
I have changed your query to delete duplicate Stock Names and all the duplicates have now been deleted.
Should I manually delete the records that are empty (no Stock_Name) or should I run a query?
If I am to run a query, could you please advise me how to do so for Blank/Empty, Stock_Name?

We do appreciate you helping us, as there are MANY duplicate records in the OLD tables that we are still using.
Nicole

 

[arnelgp]

delete * from yourTable Where ((Stock_Name) Is Null);

 

[access2010]

delete * from yourTable Where ((Stock_Name) Is Null);

Thank you, arnelgp.
I am updating our database with your assistance, while we are having a snow storm.
Crystal