# query to delete duplicate records



## pedickey (Dec 28, 2007)

How can I create a query to delete duplicate records?


----------



## jimr381 (Jul 20, 2007)

First make a backup copy of the table. Click on the "queries" object button and then click the "new" button on the Database Window toolbar. From here select the "Find Duplicates Query Wizard." Run the wizard, find the duplicates and delete one of the duplicates that you have.


----------



## rconverse (Sep 8, 2007)

I think you could also group by and then sum any figures that you want if there are different numbers to create a new table with unique records only and then delete the old table.


----------



## jimr381 (Jul 20, 2007)

I think what converse might be referring to is setting up a "Unique Values" based query where you turn on the unique values query property. The only drawback to this is that the whole record has to be typed out exactly the same as another record.


----------



## slurpee55 (Oct 20, 2004)

I have to clean duplicates a lot in y work, and one thing to remember is that two items that are not entered in exactly the same way are not, to Access, duplicates - this is much more important to remember if you are trying to remove duplicate text values, for pedickey is not a duplicate of Pedickey or pidickey....
So test for multiple fields, if you have them. Then I would suggest you copy your text field to Excel and run a pivot table on it. The text will be sorted by A-Z automatically, and you could have legitimate duplicates (more than one Jim, for instance) but if you scan it and see you have jimr381 and jimR381 and jimr38, I would check again to see if they are dupes.


----------



## JackAndCoke (Apr 26, 2007)

I have an issue like this where two fields are the exact same but the names are different. I just want to count that once. 

I have:

Store# , First Name, Last Name, Associate ID, Quote #, Retail amout, Department, Date

Does anyone know a way for me to have access match up only records where the Store#, Quote#, and Retail amount are equal then pick one of those and delete the duplicates? Can this be done with a set of queries executed in a macro?


----------



## OBP (Mar 8, 2005)

Can you provide an example of what you want to do. I am sure that it can be doen with queries, but if not a VBA Recordset will certainly do it.


----------



## slurpee55 (Oct 20, 2004)

HI Jack - surprised you didn't start your own thread, haven't seen you lately.
Go to queries, click on New. Select Find Duplicates Query Wizard. For the "Which might contain dupes..." enter those 3 fields and click ok. Select the remainder (if you want) in the next box, then click ok.
You should then be able to delete all the duplicates from the query - thus deleting them from the table.


----------

