Access/SQL - Getting around COUNT(Distinct)

2
votes

I have a table of names and addresses (FirstName, LastName, Address). I want to select the rows where the people at each address have more than one last name. For example, if John Smith and Jane Brown live at the same address. Access can't do a SELECT(DISTINCT(LastName)) so I'm not sure how to get around this.

The following gives me the addresses with more than one person but I'm not sure where to go from here.

SELECT FirstName, LastName, Address
FROM List
WHERE (Address) IN 
(SELECT Address FROM List GROUP BY (Address) HAVING COUNT(Address) > 1);

Thanks for all your help!

1
sqlms-access-2013

1 Answers

1
votes

Instead, compare the min() and max():

SELECT FirstName, LastName, Address
FROM List
WHERE Address IN (SELECT Address FROM List 
                  GROUP BY Address HAVING MIN(LastName) <> MAX(LastName));