Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting all but one record (Access 2000)

    Is there a way to delete all duplicate records except for one in a table. For example, let say I had a table with the following columns
    UserID, FirstName, LastName,

    Lets say I have 50 records with the same UserID, Username and Password. Is there a way to delete all but one copy of that record(without manually doing it in a datasheet view)

    I also have about 50 differenet records that are like this as well

    Furthermore, if this could by just using a QueryDef, that would be best [img]/forums/images/smilies/smile.gif[/img]

    Thanks

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

    Re: Deleting all but one record (Access 2000)

    The easiest solution is probably to create a query that selects uniques (either a totals query or a standard query with "Unique Values" set to Yes, and change this into a make-table query. Execute it, then rename the old and new table.

    If you want to remove duplicates in the table itself, you need a unique key field to distinguish the records, say a field named ID (not UserID, because there are duplicates in that field)
    Create a totals query based on the table that selects USerID (group by), UserName (group by), Password (group by) and ID (Min) - the latter will be named MinOfID by Access. Store this query as qryUniques.
    Create a new query based on the table, add * or an arbitrary field, and add ID. Set the criteria for ID to
    Not In (SELECT MinOfID From qryUniques)
    Change the query into a delete query. The arbitrary field will be used to remove "from" and the ID field to remove "where". Execute the delete query.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting all but one record (Access 2000)

    Good Morning...

    I tried to get a Delete query to work for this, but no luck...
    I found a way.... Maybe it'll help, or at least give you some ideas...

    I opened the table in design view and added an autonumber field and named it ID...
    I saved the table and all records showed an incremented number...
    I then created a make table query... grouping all fields and using Max for ID...
    Running this query gave me one record for each employee in a new table... and I just deleted the old one containing the duplicates...

    How does that sound?
    HTH...

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting all but one record (Access 2000)

    Oops... Sorry Hans... You beat me again... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

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

    Re: Deleting all but one record (Access 2000)

    Doesn't matter - you came up with basically the same idea... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting all but one record (Access 2000)

    Actaully I got it already, thanks [img]/forums/images/smilies/smile.gif[/img] I basically made another table, "User2" and made a query with like this:

    INSERT INTO User2 (UserID, FirstName, LastName)
    SELECT DISTINCT (UserID, FirstName, LastName)
    FROM User

    I then deleted the User Table and renamed the User2 table to User

    Thanks for the ideas [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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