Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete duplicates (2000)

    Somehow I was assigned to help a group with their database.

    They have managed to get quadruplicate records in their table.

    I don't know how & I don't care how.

    Anyone have an easy way to remove the extra records while keeping the first record?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete duplicates (2000)

    Well, if the duplicate records are missing info, then filter for those records missing info, highlight, and delete. Otherwise,in the database window click on the table, copy, paste (When you paste, paste "structure only". Now go into your new table in design view, and assign the duplicated field (hopefully some sort of order number or unique name) as a primary key.

    Next, create an append query. Choose your newly created table as the table to append to, select the table with duplicates as the data source, and drag all the fields onto the query design grid. Click run, and then click "yes" twice.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    PS - if all of the records contain different info, and you wish to save only the first one created, let us know. That gets slightly more complicated, though still easy to do <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete duplicates (2000)

    Some other possibilities:

    If the records are complete duplicates, you can create a query based on the table with the Unique Records property set to Yes. Change it to a make-table query to create a new table with unique records.

    Use the Find Duplicates Query Wizard to create a query that identifies the duplicate records. If the number isn't too large, you can remove the extras manually.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete duplicates (2000)

    Hans,

    I tried your idea thinking it would work.

    Even though the rows look alike, they are all being returned in the query.....
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete duplicates (2000)

    I had to make Unique Values true, not Unique Records....Now it is working!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete duplicates (2000)

    Glad you found it. That Unique Records didn't work means that at least one field in the record is different (perhaps an AutoNumber field). Unique Records acts on entire records, Unique Values operates only on fields that are displayed in the query.

Posting Permissions

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