Aceess Query or SQL Query: Pass or Fail

Question

the attachment excel sheet there are three tables.
Inventory Table - which is the raw data that exists
Count table - which base off the inventory table, how many times a code occured by test
Threshold table - consists of expected codes by test
 
What Im trying to accomplish:
Im trying determine the pass or fail status by test through a query or expression in access for the count table and threshold table .
Defining A Pass: expected codes count value < = threshold value, (unexpected code count value have to equal to 0)
Defining A Fail: exepected code count value > thredhold value. 
                        Also if a unexpected code happens and count value is > 0 , then that automatically makes the test a fail.
                       so its possible for the expected codes to be < = to threshold value which is pass, but if an unexpected error  happens that overrides the pass which makes that test a fail.
 
I need help writing the expression or query. the attachment  will explain everything in details , you will see visual