I have a list of people and three possible criteria for each -- fields A, B, and C. I need to extract a count of those who meet at least one of these criteria, but I don't want to count people twice who might meet more than one criteria. So, if Jennifer meets the criterion for field A, she gets counted once, while if she meets the criteria for A & B, A & C, B & C, or all three, she still only gets counted once by the query.

So I was afraid that setting up a single query with all three criteria in it would double count people, but it hasn't. Instead, it seems to count only the people who meet all three criteria.

Here's the syntax of the SQL statement:

SELECT tblMaster.[Business Segment], Count(tblMaster.[Global OHR ID]) AS [CountOfGlobal OHR ID]
FROM tblMaster

WHERE
(((tblMaster.[Job Title])="Black Belt"
Or (tblMaster.[Job Title])="Master Black Belt"
Or (tblMaster.[Job Title])="Quality Leader"
Or (tblMaster.[Job Title])="Managing Director Quality"))

OR
(((tblMaster.[In Process])="EB+ Program"))

OR
(((tblMaster.[Accomp&Cert Type])="Black Belt Certification"
Or (tblMaster.[Accomp&Cert Type])="Master Black Belt Certification"
Or (tblMaster.[Accomp&Cert Type])="Quality Leader Certification"))

GROUP BY tblMaster.[Business Segment];


So how can I pick up an individual with one and only one of the criteria? Is there a NOT within SQL that I could use to exclude people who meet more than one possibility? Or do I need to create multiple queries, ones that I can add and subtract to get the right number? That just seems too pedestrian for a program as powerful as Access.

"...it seems to count only the people who meet all three criteria."


The attached jpg shows that each of the three criteria is actually joined by AND with a fourth criterion [Capital Band]="Executive Band".

So only records that simultaneously satisfy [Capital Band]="Executive Band" and at least one of the three conditions will be counted.