Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Select and Delete from table of Dups

Results 1 to 4 of 4
Share This Thread →
  1. #1
    Jaykappy is offline Contributing User
    Join Date
    Feb 2005
    Posts
    1,278
    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
    Jaykappy is offline Contributing User
    Join Date
    Feb 2005
    Posts
    1,278
    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?

  3. #3
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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

  4. #4
    Jaykappy is offline Contributing User
    Join Date
    Feb 2005
    Posts
    1,278
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates