Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Duplicate Records (97 SR2)

    I would like to author a query which will delete duplicate records.. however, I can't seem to nail it down... How might I do this?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Delete Duplicate Records (97 SR2)

    The real issue with duplicates is that you only want to delete one of the duplicate records, so you have to figure out which one to delete (you don't want to delete both I presume). When we get that situation we typically use the query wizard to create a select query that shows you the duplicate records and then delete one of the records manually. Of course you have to make sure you don't have records in other tables that are referring to the record you are deleting. All in all, not a simple subject to deal with. It's generally easier to prevent the entry of duplicate records than to deal with duplicates after the fact. Unfortunately, there are times when duplicates must be allowed, e.g. people names. Hope this comiseration helps.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicate Records (97 SR2)

    Well.. my data is coming from Excel, so there are going to be duplicates...

    My key, a tracking number is critical. I need there to be only one instance of each one. It doesn't matter which is deleted.

    Has anyone worked out a process for this?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Duplicate Records (97 SR2)

    Doesn't Access have a built-in duplicates query?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicate Records (97 SR2)

    Add a field whit a unique value for each record
    Write code to loop through the table, indexed on the unique field.
    For Each record in the table, do a find for Key = Current Key and Unique Field greater the current unique field.
    If You find one, delete this record.
    Francois

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicate Records (97 SR2)

    Well, if you're importing from excel and can live with the error messages, just make your tracking number a unique index or primary key. Access will complain about key violations, but the first of each will be imported fine.

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

    Re: Delete Duplicate Records (97 SR2)

    Excellent suggestion - to add a bit more detail, you need to design the table you are going to import into before you do the import. Otherwise, if you import to a new table, it won't have a primary key, and you won't be able to assign one because of the duplicate values. All this discussion presumes that the records are exact duplicates, so you really don't want more than one, or at least the fields that you want to import are all duplicated. You will get an error message during the import, and it will tell you that the errors can be found in an error table.
    Wendell

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicate Records (97 SR2)

    That seems to work well, can you think of a way to automate the process? It would be nice if I didn't have to click on 'Yes' all the time....

    thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Delete Duplicate Records (97 SR2)

    Deleting EXACT duplicate records is always a challenge. I deal with dup records all the time because lots of my data comes from a data mining program which is riddled with duplicate links.

    You can make a blank copy of your table and set a multiple key on a good number of fields then paste your data into the new table. The number of fields you can make a key is around 10 or so. This will eliminate dups when you import the dup ridden data.

    Another method I used is a little more exportable and can be added to a library. What I do is via code, add an autonumber field and then iterate the fields collection of the target table and concatenate all fields into a long string contained in a recordset and include the autonumber value. The recordset is based on an sql string of the dup containing table in ascending order. I then go to the first record and see if the concatenated string in the next record is the same. If so, I use the autonumber field as a parameter to delete the dup in the original table. I then see if the next record is identical and so forth. If the next record is different, then we store that value and peek if the next record is the same. It goes on... When done, the autonumber field is deleted from the original table. One thing to be wary of if the original table has an existing autonumber field, you will have to deal with it first.

    The best thing about this scheme is it's independent of access key field restrictions and will adjust to data in any table irregardless of the number of columns.

    Dave Mack

Posting Permissions

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