Looping through query to find highest priority item

[gungrave19]

I have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.

Tables
tblItem
tblFormula
tblFormula_Item_JNT

There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".

So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once. I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.

Just not sure how to tackle this one. Any help would be appreciated. Thanks!

 

[plog]

You and I know that High is higher than Medium and Medium is higher than Low, but using just those words, the computer won't be able to make that determination (sorting ascendingly, 'High' comes before 'Low' which comes before 'Medium'). So, you need to change your Priority to a numeric field (Low=1, Medium=2, High=3).

From there you link your tables as normal and make your current query into an aggregate query (using the Sigma in the ribbon), Group By Item, get the Maximum value for your Priority and use the WHERE clause on Item_Status to set it to False.

 

[gungrave19]

Thanks plog! I was able to get it working using your suggestions.