find a value that does not "contain" a certain criteria?

Question

I'm building a query using microsoft access 2016.

But i'm bugged out with an issue. I want all companies that doesn't have any type 01 in their activities.

the problem is, if I specifiy "Not like '01'", I will get all the activies of said company that is NOT 01. But what I would like is to find all the companies that does NOT have any '01' in their record. How would I do that ?

Answer 1

You could use aggregation:

SELECT companyId
FROM yourTable
GROUP BY companyId
HAVING SUM(IIF(activity = '01', 1, 0)) = 0;