Greetings to all,

I have duplicate records in my table (i.e. there are two or more records that are identical, and I only want to keep one of them). Is there another way to delete the duplicate records other than one at a time? Assigning primary keys and appending only unique records to a new table is not an option because in some instances, I want to maintain duplicate values within a field (i.e. the desire to delete is based on more than one field)

Any help would be greatly appreciated

Cheers, Lori (An Access amateur)

Lori:

You could add a field as a YES/NO field and then check off the ones you want deleted and then run a delete query that has the YES/NO field = YES in as criteria.

Hi,

Thanks for the reply. That would work, but I have 1000s of records and am looking for an automated way to do this rather than going through and checking them off

If you have the duplicate query do you really have THAT many duplicates? How many duplicates are we talking about?

If you have the extra field and add it into the find duplicates query that should let you check off the ones you want outta here and then you can run the delete query.

Also, do you think Access will be able to read your mind about which ones to keep and which to not keep? You have to tell it somehow and I would think that using an extra checkbox field in with the find dups query would aid you.

Well, as it stands I have 133 duplicate records to deal with - The scoop is that this is just an excerpt of the main table. I imported a dbf file into Access to test the possibility of using Access to eliminate my duplicate records problem.

Lori

Well, if the problem were to eliminate ALL duplicate records then that would be an automated fix. Because you said you must keep SOME duplicates then it comes down to you telling Access what you want to keep. So, unless there is some common ground among those records which you want to keep, then the completely automated solution does not exist.

You have to tell it somehow what you want to get rid of; there's no way around it - sorry.

But, I do have some good news for you. You could use an UPDATE query with the Dups query as the basis for it and then update all of the checkboxes to True in that query.

Let me know if that doesn't make sense. And then you can UNCHECK any you want to keep. Would that make it any easier?

Lori & Bob,

Also, *maybe* there's a date field and she can just keep the most recent
or oldest.

Wayne

Lori & Bob,

Also, *maybe* there's a date field and she can just keep the most recent
or oldest.

Wayne

Right Wayne, and that was part of my last post. There has to be SOME kind of common thread amongst the records to completely automate it. Sans that, ya gotta go do some manual work.

Thanks for all of the replies.

I have not yet tried Bob's last recommendation, though I have attached a link to some software that claims to do exactly what I want, however, I would have to pay $19.

http://www.downloadjunction.com/product/store/30187/index.html