Pass or Fail Query

Question

We have an inventory table and a thresholds table. We need to determine the status of each item and put the status of each item into each record of the item. The status is either true or false. The status is true if any of the following are true.

• The first character of the Item is an "E" or "0" (zero).
• The item is not in the Threshold Table (joined using both Item and Type?).
• If the the item is in the Threshold Table then the number of Serials for it is greater than the threshold for it. The two tables need to be joined using both Item and Type.

We want to put all that into an Access query (SQL) for execution as a stored procedure.

The following shows the fields of each table.

• Inventory Table
  • Type
  • Item
  • Serial
• Threshold Table
  • Item
  • Type
  • Threshold

 
Can someon help me design this query