Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate records with more than 10 fields (97/SR-2)

    Is there a way to delete duplicate records from a table based on more than 10 fields?

    I have a table with about 100,000 records, each with 97 fields (not my creation, mind you). For any record with an exact duplicate, I would like to delete both records. I'm assuming that this would take hours to perform. I don't care how long it takes; I have all night.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    I think what you may want to do is to create a query which finds the duplicates with the 10 fields, and then create a second query that looks at the remaining fields to determine if they are duplicates based on the remaining fields that you haven't checked. You may actually want to do a make table to speed up the process. The other alternative is to create a find dups query with the Wizard, and then change the IN clause to add the additional fields. It is likely to be really slow however. To some extent the process will also be dependent on how many dups you actually find. Hope this helps.
    Wendell

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    I made a post a while ago regarding removing exact dup records. I get tons of dups from a data mining program which draws leads on stored data thereby giving dups inherently. Take for instance "blue eyed guys in New York" and "Dead Beat Dads". There will be two exact entries for a blue eyed dead beat dad.

    Attached is my solution. Hope it helps. I think it's pretty straight forward...turn each record into one big string and then sort the table and remove dups.

    Cheers,

    Dave
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Jan 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    My version of Access can't open the attached database (dup nukem). It sounds like a great idea, though.

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    Sorry, I posted the A2K version. Here is a the file converted to A97.

    Cheers,

    Dave
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Jan 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    Thanks for the speedy delivery. That module seems to work wonderfully on the test table, buy works quite differently on my data. When I run it on my table, it tells me that it's done after about 10 seconds and removes no records. The table I am using for my testing is 138,000 records long, and every record except two have duplicates. Each record has 97 fields, for a total of 1200 characters. I'm pretty sure that the module can't even begin to sort that many records in 10 seconds. Any ideas as to why my table makes the program think it's done when it's not?

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    Could you send me a table with about 100 records of your data in it? I never tested it with more than 30 fields. If I have a design flaw or maybe hitting some kind of application limitation, I sure would like to get it fixed. You can reply directly to me if you do not want to post your data.

    Dave

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate records with more than 10 fields (97/SR-2)

    Hi David

    Was there any outcome you want to share with forum on the test that was sent to you.

    John

Posting Permissions

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