Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: Writing SQL code to delete duplicate records

    I am hoping someone might be able to help. I have a database that I am trying to delete the duplicate records (there is no way to stop these as its from an excel spread sheet that has over 70,000 records)

    I have created a couple of queries using SQL to get the total number to around 1400 records, however there are still duplicates where a customer has entered the postcode for instance with and then again without a space inbetween.

    What I was hoping for, is to do a SQL query to delete all duplicate records searching on the First Name, Surname and postcode, however I need it to show the results with all columns (Title, First Name, Surname, Address, Address2, Town/City, County, Postcode)

    I have that this makes sense to someone as I have been searching everywhere for an answer, but nothing really makes sense (as this is the first time I have started using SQL coding so I am a complete novice when it comes to this.

    I am currently using Microsoft Access 2010

    Thank you in advance!!

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    The problem is going to be where you say you want to show all columns. Since post code can be different it will show the duplicates. Is there anyway that you can "clean up" the post code data? Like removing the space and then you can try to query out Distinct records.

    C

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    I could do. I have found out since this morning, that every cell has imported with a Space infront of each word, so i am working on a code to remove the first charactor in each cell, and this may help...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MuppetBoy View Post
    I could do. I have found out since this morning, that every cell has imported with a Space infront of each word, so i am working on a code to remove the first charactor in each cell, and this may help...
    Use a query, try:
    Code:
    UPDATE [Your table name here] SET
        Title = Trim(Title), 
        [First Name]= Trim([First Name]), 
        Surname = Trim(Surname), 
        Address = Trim(Address), 
        Address2 = Trim(Address2), 
        [Town/City] = Trim[Town/City]), 
        County = Trim(County), 
        Postcode = Trim(Postcode);
    Have a nice day!

  5. #5
    Join Date
    Dec 2012
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    Use a query, try:
    Code:
    UPDATE [Your table name here] SET
        Title = Trim(Title), 
        [First Name]= Trim([First Name]), 
        Surname = Trim(Surname), 
        Address = Trim(Address), 
        Address2 = Trim(Address2), 
        [Town/City] = Trim[Town/City]), 
        County = Trim(County), 
        Postcode = Trim(Postcode);
    That worked perfectly!!!

    Thank you

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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