Solved Met or Not Met - Group By with conditions (1 Viewer)

[jack555]

Having a scenario where to auditors review and score different questions under few categories.

I would like to mark the category as per the below criteria.

1. if any of the questions in a particular category is "Not Complaint" then the category is "not compliant".
2. If all the questions in the category are "Not Applicable" then the category should be marked "Not Applicable".
3. If there is no "not Complaint" and any "compliant" or then should be marked as "compliant"

where multiple users, categories and audits happen.

below is the example table.

A	B	C	D	E
User1	Audit1	Category1	Question1	Compliant
User1	Audit1	Category1	Question2	Not Compliant
User1	Audit1	Category1	Question3	Compliant
User1	Audit1	Category1	Question4	Not Applicable
User1	Audit2	Category1	Question1	Compliant
User1	Audit2	Category1	Question2	Compliant
User1	Audit2	Category1	Question3	Not Applicable
User1	Audit2	Category1	Question4	Compliant

And the result would be like this.

User1	Audit1	Category1	Not Compliant
User1	Audit2	Category1	Compliant

With beginner level expertise in Access, I tried group by or totals query but couldn't get the expected outcome.

 

[arnelgp]

see your guidelines again.
the result are all "non compliant"

see Query2.

 

[jack555]

see your guidelines again.
the result are all "non compliant"

see Query2.

Sorry, edited the mistakes in question. Please check now.

 

[arnelgp]

see again Query2.

 

[jack555]

see again Query2.

Exactly what I was looking for. Always you are solving the problems in a breeze. You are a star.