Query to find multiple data with criteria

[hunoob]

Hi there Everyone. I have attached a test file to show you what is my problem. So I have two tables. Risk table and Controls table. The controls are connected to the risks, so one risk can have many controls. The controls can be efficient or not efficient. I would like to make a query which will show only those risk(s) which have only
"not efficient" control points. In my file this query should find only risk no. 2.

Please help me because I cannot make the query.

Thank you very much in advance and have a nice weekend!

 

[recyan]

One way could perhaps be :
1) A query which finds distinct RiskID's having efficient & then
2) using the wizard to design an UnMatched Query on the table Risks RiskIDs & the query in Step 1

Thanks

 

[nanscombe]

In the attached database (Access 2003 format) I did this using 3 steps (queries).

Step1_EfficientControlsByRisk - Count The 'efficient' Controls by risk
Step2_NotEfficientControlsByRisk - Count The 'not efficient' Controls by risk

Step 3 - I then combined tblRisk with the 2 queries above and returned any records where (the number of 'not efficient' Controls > 0) and (the number of 'efficient' Controls = 0 or Is Null) in the query Result_RisksWithNotEfficientControlsOnly.

The only row returned was:

Risk ID: Risk Name: Risk Description: Efficient Controls: NotEfficientControls
2: Password risk: the risk of forgetting the password:  : 2

 

[hunoob]

Hi All! Thank you very much for your replies. Nanscombe I tried your (very clear explained) solution and it worked like a charm! Thank you very much again for your quick help!