Filter in a query (1 Viewer)

[John liem]

I try to run a query from a customer table which output only double records and filter out unique single records.
So, ONLY the same customers with different home addresses will appear on the query result.
Thanks.

 

[calmwind]

Try making three queries.

Table (tbl)
ID
Name
address

1st Summary Query (SumQuery)
SELECT tbl.Name, Max(tbl.address) AS MaxOfaddress
FROM tbl
GROUP BY tbl.Name;

This gives you all unique Names



2nd Unmatched Query (UnmQuery)
SELECT tbl.Name, tbl.address
FROM tbl LEFT JOIN SumQuery ON tbl.address = SumQuery.MaxOfaddress
WHERE (((SumQuery.MaxOfaddress) Is Null));

This gives you all the Names that are listed twice



3rd Select Query (DupQuery)
SELECT tbl.Name, tbl.address
FROM UnmQuery INNER JOIN tbl ON UnmQuery.Name = tbl.Name;

This gives you all the Names with both addresses



Hope that helps

 

[John liem]

Thanks calmwind, I'll give it a try.