putting records in new table that don't have 6 or more different addresses for same name - Microsoft Access

Question

Hey all,

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.

Thanks for response

Answer 1

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") )