#1
  1. No Profile Picture
    Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Feb 2005
    Posts
    1,280
    Rep Power
    36

    Select and Delete from table of Dups


    I have a table with Duplicates determined by 3 fields. There is no other way to get my data into this system....

    What I need to do is create a query that will grab the first instance of each duplicate and delete it....

    HOW can I do this?

    Ex

    ID Field 1 FIeld2 FIeld3
    1 3445 10/10/2012 Good
    2 3445 10/10/2012 Good
    3 9889 5/5/2011 Fair
    4 9889 5/5/2011 Fair
    5 9889 5/5/2011 Fair

    What I want left is

    ID Field 1 FIeld2 FIeld3
    1 3445 10/10/2012 Good
    3 9889 5/5/2011 Fair
  2. #2
  3. No Profile Picture
    Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Feb 2005
    Posts
    1,280
    Rep Power
    36
    I have read that I can copy the Structure of the table and then make one field that I dont want duplicated and make that the Primary Key and then copy the records into that table...thus eliminating duplicates.....
    But my table is defining the Duplicate records over a couple fields......not 1...can only assign one Primary Key....so that wont work.....can I do something similar but defining 3 or 4 fields to determine the Duplicate records?
  4. #3
  5. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Try compound primary key. Set multiple fields as primary key.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Feb 2005
    Posts
    1,280
    Rep Power
    36
    Thanks....
    was just coming back to let you all know I think I figured it out....I created another field and simply concatonated the 3 fields into one.....then used this as a Primary Key...
    Thanks

Similar Threads

  1. Access Help. Trying to select certain records in table to delete
    By mcranda in forum Microsoft Access Help
    Replies: 6
    Last Post: May 24th, 2012, 11:23 AM
  2. Delete Table still returns rows on a subsequent select
    By LizSmith in forum Microsoft SQL Server
    Replies: 2
    Last Post: June 1st, 2010, 07:32 PM
  3. Replies: 7
    Last Post: February 5th, 2010, 12:40 AM
  4. Is there any way to select specific records and delete them?
    By xtgeminiman in forum ASP Development
    Replies: 6
    Last Post: December 11th, 2005, 06:43 AM
  5. Convert a SELECT into a DELETE statement
    By Chris@Home in forum Microsoft SQL Server
    Replies: 1
    Last Post: March 8th, 2004, 01:50 AM

IMN logo majestic logo threadwatch logo seochat tools logo