IIf statement based on multiple fields and need to set up table?

[heynay01]

I have a database where I transfer data from Excel sheets into for the purpose of tracking quality control reports scores and comments. The data that I have pulled into my table include: Consultant, Score (the numerical value), and a bunch of comments and other fields.



The problem is that the consultants fall into 1 of 3 job categories (Technical, Sr, Regular), and each job category has its own rating scale for Exceeds, Met, Partially Met, Did Not Meet. I need to figure out how to correlate the rating scale with the consultant's job category andthe QC score.

I'm at a loss for an IIF/AND statement for a query that will check job category and score and return the correct rating (probably not the best way to do it anyway ), or how to create a table to put the range of scores in and join in a query.

Technical (job category)
(rating) (score)
Exceeds = 96-100
Met = 93-95.99
Partially Met = 89-92.99
Did Not Meet = <=88.99

Sr.
Exceeds = 93-100
Met = 90-92.99
Partially Met = 87-89.99
Did Not Meet = <=86.99

Regular
Exceeds = 91-100
Met = 88-90.99
Partially Met = 85-87.99
Did Not Meet = <=84.99

Any help, insight, suggestions are greatly appreciated!

[pbaldy]

I would probably have a table with fields

JobCategory
Rating
ScoreLower
ScoreUpper

so an example record would be

Technical
Exceeds
96
100

That makes if fairly simple to find the rating for any combination of category and score. The criteria in a DLookup or query would basically match category and score >= ScoreLower and score <=ScoreUpper. You could also use a non-equi join in a query.

[heynay01]

Thanks for pointing me in the right direction. Worked like a charm!

[pbaldy]

Happy to help!