Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Covington, LA
    Posts
    46

    Unanswered: delete duplicate records

    i have a table with dupliucate records in one field, this field is the invoice number so there should only be one. most are duplicates from being entered in twice. i have a find duplicate query already, is there sql i could add to it to make it delete all but one duplicate record. most invoice numbers have only been entered twice, some have been entered 3 times. can i add a calculated count field and delete the records by something like sum of [invoicenumber]-1... i'm shooting in the dark but it has to be done.

    thanks very much!
    Unique like everyone else,
    Clark
    -------------------------------------------
    POSTING IS THE TERRIBLE SECRET OF SPACE!

  2. #2
    Join Date
    Sep 2001
    Posts
    46
    Since your table have dupliucated records [Invoice Number] and do you know that in actual/real world, Invoice number must be unique which mean that Invoice number never to be duplicated.

    For your situation,your problem is not eliminate the dupliucate records but is to re-design your database Integrity.

    Note : If two same Invoice number --- > two different customers
    If you delete one of it,---> mean there only one transaction only.

  3. #3
    Join Date
    Mar 2002
    Location
    Covington, LA
    Posts
    46
    thanks for the response. here is the real kicker. RequestID was the key field in the old database, and InvoiceNumber is the new key field. we are changing over to a new database and saving what data we have. i wrote a program that put the last 3 digits of the InvoiceNumber in a newly added JobID field, and then it reformats the InvoiceNumber to take out the -'s. the new database referential integrity should be correct. but in order to finish copying the old records over i need to delete the ones that are just copies. or am i missing something? the new DB is much better i think.
    Unique like everyone else,
    Clark
    -------------------------------------------
    POSTING IS THE TERRIBLE SECRET OF SPACE!

  4. #4
    Join Date
    Sep 2001
    Posts
    46
    In query for Invoice number
    Total : Count
    Criteria : >1

  5. #5
    Join Date
    Mar 2002
    Location
    Covington, LA
    Posts
    46
    hello everyone out there on the www reading this. i discovered a solution. This help article explains it all. It's very simple.
    Unique like everyone else,
    Clark
    -------------------------------------------
    POSTING IS THE TERRIBLE SECRET OF SPACE!

Posting Permissions

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