Deleting duplicates

[Guest]

Hi, I went through a lot of the answers to similar questions, but there are
always issues, so I though I'd give my specific situation and hopefully get a
working answer.

I have two databases, 'Main' and 'Detail.' They are related by a column
called 'id'. The Main and Detail databases have a relationship so that there
is a + sign next the each row in Main, and clicking it will show you the
information for that id # in Detail. In Main, there are these columns - id
(which has a primary key), first name, last name, phone, fax, email.

What I need is to delete all the rows in Main that have duplicates for both
First Names and Last Names. I would like the kept one to be the one last
entered (higher ID). I would also like to delete the corresponding row in
the Detail database, if possible.

Like I said, I've tried several suggestions I read, but there have always
been issues like the delete query running for an insanely long time and never
finishing (the database is only 6,000 records) or the columns for phone, fax,
and email not being kept in the final database.

Any help is greatly appreciated.

 

[Allen Browne]

Simplest solution is to create a table without the duplicates.

1. Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General.
Explanation of why:
http://allenbrowne.com/bug-03.html

2. In the Database window, select your table.
Copy (Ctrl+C).
Paste (Ctrl+V).
Tell Access you want "Structure only", and choose a new name.
Ultimately, this will hold the de-duplicated data.

3. Create aquery that gives you unduplicated records.
It will GROUP BY the critical fields, and give you Max of the ID.
Save the query.

4. Create another query that joins that to your original table.
This one gives you all the fields, but only for the de-duplicated records.
Change this into an Append query.
Tell Access to save to the table created at step 1.
Run the query.
Check that the new table has the right records.

5. Open the Relationships window (View menu.)
Break all relationships to your original table.
Delete the original table.
Compact the database.
Rename the new table to the same as the old if you wish.

6. Now to delete the related records.
The query will be something like this:
DELETE FROM Table2 WHERE NOT EXISTS (SELECT ID FROM Table1 WHERE Table1.ID =
Table2.ForeignID);

7. Recreate the relationships.