delete 4 out of 5 records where name field and address field repeat more than 5x

[jal]

Well, I haven't been testing my code. Here's a correction (also untested).

SELECT F.*
INTO KeepThese2
FROM [The Final Query] as F
INNER JOIN
(
    SELECT [COLOR=red]DISTINCT[/COLOR]  F.names_1
    FROM [The Final Query] as F
    LEFT JOIN
    (
        SELECT names_1
        FROM [The Final Query] as F
        GROUP BY names_1
        HAVING COUNT(*) >=6
    ) as NamesToReject
    ON NamesToReject.names_1 = F.names_1
    WHERE  NamesToReject.[COLOR=red]names_1[/COLOR] IS NULL
) as NamesToKeep
ON NamesToKeep.names_1 = F.names_1

 

[jal]

I also added the DISTINCT keyword to help reduce the chance of creating dups.

 

[johnmerlino]

Thanks for all your replies. I ultimately got it working with this:

SELECT *
FROM final_output AS f 
LEFT JOIN (
     SELECT t.fullname 
     FROM final_output AS t 
     GROUP BY t.fullname 
     HAVING Count(t.fullname)>=6)  AS e
ON f.fullname=e.fullname
WHERE e.fullname Is Null