Deleting all but one record (Access 2000)

Topic

New Reply

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

Thanks

Reply | Quote

Replies

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.

Reply | Quote

Oops… Sorry Hans… You beat me again…

Reply | Quote

Doesn’t matter – you came up with basically the same idea…

Reply | Quote

Actaully I got it already, thanks 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

Reply | Quote

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…

Reply | Quote