If the names repeated is more than 6 and there are six different addresses for that same name, then I don't want to output them to the KeepThese table:
harry baker 1 street
harry baker 2 street
harry baker 3 street
harry baker 4 street
harry baker 5 street
harry baker 6 street
donald ross 11th street
So harry baker should be excluded from new table. donald ross should be included in new table.
This is existing query that I have:
SELECT F.* INTO KeepThese
FROM final_output AS F
INNER JOIN (SELECT DISTINCT F.fullName FROM final_output
AS F LEFT JOIN (SELECT fullName FROM final_output
AS F GROUP BY fullName HAVING COUNT(*) >=6)
AS NamesToReject ON NamesToReject.fullName = F.fullName WHERE NamesToReject.ID IS NULL)
AS NamesToKeep ON NamesToKeep.fullName = F.fullName;
This returns "Enter Parameter Value NamesToReject.ID" But this is best I could come up with.
How about :
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 Ex
ON f.fullname=Ex.fullname
WHERE Ex.fullname Is Null
Up to this point, any name and address combination with less than six of the same name will be shown.
If this next statement is added, only one of each name will be shown with one address for that name, chosen at random.
AND Nz([address],"None") In (
SELECT TOP 1 Nz(address,"None")
FROM final_output t
WHERE t.fullname = f.fullname
ORDER BY fullname, Nz(address,"None") )