Using a query to delete duplicates

[Lorimare]

Greetings, I ran a query on a table to check for duplicates as follows:
SELECT tbl_Final.phone_num, Count(tbl_Final.phone_num) AS CountOfphone_num
FROM tbl_Final
GROUP BY tbl_Final.phone_num
HAVING (((Count(tbl_Final.phone_num))>1));

The query will return two rows, phone_num and count of the duplicates.

Now is there anyway I can create a delete query or any other query that will delete all but one of a particular phone number?

Again if the query I run checks for duplicate phone numbers and I get one of: 8168887777 with a count of 8 (is in the table 8 times, and another phone number of 8167778888 with a count of 3.........is there a query I can make to delete all but one of each phone number?

 

[hneal98]

The best and easiest way to do this is to put those records that duplicate into a table and then run a query against that table stating that any records in that are in the dup table should be deleted. Then copy the records from the duptable into your original table. You could probably do this using sub queries, but if you are working with a large amount of data (40,000 records and up), it would take a long time.

 

[Tranman]

jonair,
There is another way, which MS suggests in HELP:

Create a copy of the structure only of your table. (In the database window, just copy, and paste the table name, and when it asks, choose 'structure only').

In the copy, make the phone number the primary key.

Run an append query to add all of the rows from the first table to the second one.

When the query runs, it will say that some of the rows were not added due to key violations (or something like that). Just tell it to go ahead and add the other rows (I think it's the 'YES' response).

Tranman