Need Help deleting duplicate records

[mystuff]

Hi,

I would like to delete the 2nd record of duplicates. My table is storing contact information such as First Name, Last Name, address, city, state, etc.

I would like to delete the duplicate of all records where First Name, Last Name, Address, City and State are equal. I have a query that shows me all of the dups, but I can't figure out how to delete the 2nd occurance of each dup.

Thanks in advance.

 

[r937]

unless there's a primary key, your best bet is to run the query that detects the dupes, make sure it's using DISTINCT or GROUP BY so that there's only one row for each, save the results into a temp table, delete every row from the main table where there's a match in the temp table, then run an insert/select to add the distinct rows back from the temp table into the main table

oh, and then declare a UNIQUE constraint on all the fields of interest, so that dupes never happen again

rudy

http://r937.com/

SQL Consulting

 

[mystuff]

I created the temp query, but when I try to run a delete query against the main table I keep getting "Operation must use an updateable query" error message. Maybe I am doing the delete query wrong.

Any idea?

 

[r937]

no idea without seeing your queries

rudy

http://r937.com/

SQL Consulting

 

[chtullu]

Hello,

I had the some problem and I executed the following make table query

SELECT DISTINCT * INTO temTable
FROM tblImportedBrinks

tblImportedBrinks is the table with duplicate rows and I populated the temp table with only unique rows. I then deleted all the records from tblImportedBrinks and set the Database Id field as a unique field, so I wouldn't have the same problem with duplicates again. I then ran a append query that populated the now empty tblImportedBrinks table from the temp table.

Juan

 

[TomCologne]

Check this out:

Delete Duplicates save One!
thread701-1013971


TomCologne