query to delete duplicate records

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 
Joined
Sep 7, 2007
Messages
191
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

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 
Joined
Oct 20, 2004
Messages
7,837
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.
 
Joined
Apr 26, 2007
Messages
152
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

Joined
Mar 8, 2005
Messages
19,895
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.
 
Joined
Oct 20, 2004
Messages
7,837
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top