how to "de-dup" a database?

[jkat]

Hey, I have a quick question. I have a database in MSSQL 7 and it has about 16,000 records. I want to be able to remove all duplicat records from the database based on a certain field (ie all records with the same accntID be removed) so i only have a remaining database of unique account numbers and associated fields. any information would be greatly appreciated! thanks!!

thanks again!

 

[Rohdem]

Access has a built in 'find duplicates query wizard' that should get you started. Once you use the wizard to build the query, change it to a delete query. Mike Rohde
rohdem@marshallengines.com

 

[Rohdem]

After thinking about it for a few more seconds, I realized you can't just convert it to a delete query, because this will remove ALL records that have duplicates instead of leaving one copy of each duplicate.

You might just create a new table with a primary key on the field that you want to filter out the duplicates. Then run an append query. Access will not allow you to append duplicate primary key values. Mike Rohde
rohdem@marshallengines.com

 

[tlbroadbent]

There are a number of considerations when deleting duplicates. Here are some sources you can read to get some ideas about the options.

http://support.microsoft.com/support/kb/articles/q139/4/44.asp

http://www.swynk.com/friends/boyle/removeduplicate.asp

Terry

http://members.home.net/tlbroadbent/index.htm