"And" function

[Paula Kutacova]

Querying a table which has people and colors they like. Every person has multiple entry as they like different colors. Question: How do I get a list fo poeple who like 2 or more different colors. Lets say: give me a list of people who like yellow, red and blue.
Thanks, Paula

[June7]

Getting a list of people who like more than 1 color is a simple aggregate (GROUP BY) Totals query.

SELECT PersonID, Count(Colors) AS CountColor FROM tablename GROUP BY PersonID HAVING Count(Colors) > 1;

If you want to filter based on specific colors with the AND operator that is tricky. Maybe a CROSSTAB query will help, otherwise I expect will need custom VBA function.

[ranman256]

You could use a 'search' table that has the 3 colors in it. It will return only those that match any in the list...do a count on this to get those that have count=3

[Paula Kutacova]

yes, that was my thinking as well as it works. I would import 3 excel sheets with the info into the same table and group and count >3. What do you mean by the "search table". Thanks, Paula