Update: using sql to keep only a single record where both name field and address field repeat in 5+ records - Microsoft Access

Question

Hey all,

I am trying to delete all but one record from table where name field repeats same value more than 5 times and the address field repeats more than five times for a table. So if there are 5 records with a name field and address field that are the same for all 5, then I would like to delete 4 out of 5. An example:

id name address
1 john 6440
2 john 6440
3 john 6440
4 john 6440
5 john 6440

I would only want to return 1 record from the 5 records above.

Update: Thanks for response but I'm still having problems with this.

1) I create a table called KeepThese and give it a primary key id. 2) I create a query called delete_1 and copy this into it:

INSERT INTO KeepThese
SELECT ID FROM
(
SELECT Min(ID) AS ID
FROM Print_Ready
GROUP BY names_1, addresses
HAVING COUNT(*) >=5

 UNION ALL

SELECT ID FROM Print_Ready as P
INNER JOIN
(SELECT Names_1, addresses
FROM Print_ready
GROUP BY Names_1, addresses
HAVING COUNT(*) < 5) as ThoseLessThan5
ON ThoseLessThan5.Names_1 = P.Names_1
AND ThoseLessThan5.addresses = P.addresses
)

3) I create a query called delete_2 and copy this into it:

DELETE P.* FROM Print_Ready as P
LEFT JOIN KeepThese as K
ON K.ID = P.ID
WHERE K.ID IS NULL

4) Then I run delete_1. I get a message that says "circular reference caused by alias ID" So I change this piece: FROM (SELECT Min(ID) AS ID to say this: FROM (SELECT Min(ID) AS ID2 Then I double click again and a popup displays saying Enter Parameter Value for ID.This indicates that it doesn't know what ID is. But print_ready is only a query and while it has an id, it is in reality the id of another table that got filtered into this query.

Not sure what to do at this point.

Thanks for response.

Answer 1

CREATE TABLE isolate_duplicates AS dont sure it work for access, beside you should give a name for count(*) for new table.

This maybe work:

   SELECT DISTINCT name, address
      INTO isolate_duplicate
      FROM print_ready
      GROUP BY name + address
      HAVING COUNT(*) > 4

   DELETE print_ready
      WHERE name + address
      IN (SELECT name + address
             FROM isolate_duplicate)

   INSERT print_ready
      SELECT *
         FROM isolate_duplicate

   DROP TABLE isolate_duplicate

Not tested.