Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    1

    Unanswered: Deleting duplicate records in table

    Is there an easy way to do this?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One question would be: Are you trying to delete records where the entire record is duplicated (other than the autonumber/primary key field) or where just one of the fields is duplicated?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One way I've done it is (below are some GENERAL guidelines I've used in the past. Since each situation is a little different, you'll need to see what works best for you) You can also write a bit of code if you're willing to spend the time:

    1. Make the Find Duplicates query.
    2. Change this to a make-table query and make a table (i.e. DupsTable)
    3. Make another table with Unique records of this Find Duplicates query (i.e. UniqueTable).
    4. Link the DupsTable (or UniqueTable depending on your situation) with your data table in a query on the duplicated field (or primary key - again, depends on your situation). Change the query to Dynaset (Inconsistent Updates).
    5. Open the query, delete all the records.
    6. Append all the records in the UniqueTable to your main data table.

    Be careful though. This is assuming you don't care about autonumber and primary key fields or data that might be different in one record verses another even though there is a duplicate on one of the fields. Since I don't know your specific circumstances, you'll have to tweek what fields you have in the queries above. If you have tables linked to your duplicate data table, the whole thing gets rather complex and I'd recommend taking it very slow, making constant backups of the entire data tables before deleting any data. Nothing worse (in my opinion when doing this) then ending up with orphaned records or deleting data by mistake.

    Also keep in mind, what you think is a duplicated record is not always a true duplicate. You might have 1 field in a record (with say 50 fields) where the data in that 1 field is different than the other record. I say this only because there's almost always a way for data entry people to find a way to duplicate a record and then enter 1 thing different (i.e. phone number or address is slightly different).

    For example, I had what I thought was the best method for not allowing duplicates on customers only to find 'Tom Jones' could be entered: 'Thomas Jones' or 'Jones Thomas' or 'Tom & Peg Jones' or 'Thomas & Peg Jones' or 'Tom & Peggy Jones', etc...etc... This is the same for the address. I even had a primary key on the first character of the first name + the last name (they still found a way to duplicate it - i.e. William verses Bill) as well as Last name + first 5 characters of the address and about a dozen other methods. The only I've found was a good way to prevent duplicates was when we collected the social security number (or credit card #'s) and did credit checks on the man or woman's ss# so it couldn't be mistyped. Then it was fairly easy to make sure the 2 same ss#'s were not entered. Even phone numbers were not totally safe and changed.

    If anyone thinks they have a fool-proof method (without collecting ss#'s), I'd be extremely doubtful and 100% sure I could enter a duplicate (a challenge for anyone who thinks they have a method which doesn't allow any duplicates collecting just the customer's name and address (keeping in mind that I might be a customer who's moved and changed my address or phone number) - I guarantee I'll break it and enter a duplicate record.) Disregarding of course, those idiotic routines where each customer is assigned a Customer number and when the customer calls they have to know what their customer number/loan number is, etc.. (don't you hate that when you call a company and have to know what your customer number is? - like I have that memorized or carry it with me - the only numbers I can remember by heart are my ss#, phone numbers, and one of my credit card #'s.)

    I know I've gotten of course here but I used to pull my hair out trying to find ways a customer entry could not be duplicated and we weren't allowed to collect ss#'s or ask the customer's phone number.
    Last edited by pkstormy; 05-04-07 at 18:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •