Chained IIF statements in this query referring to a specific record. Help?

[Steven Seagal]

I have a list of 200 employees, supervisors, and managers. Each has a job grade level A, B, C. I already have a table that acts as a key that lets you know which position gets which job grade.



So the "key" would look like this [JobKey]:

Position ---- Grade
Employee ---- A
Supervisor ---- B
Manager ---- C

Now in this list of 200+ employees, I already know the names and what position they are. I need to make a matrix with just employee names and their job grades.

So I have a table that has this - let's call it [EmployeeList]

Name Job
Al --- Employee
Bob --- Supervisor
Carol --- Manager

I want to create this kind of query:

Al - A
Bob - B
Carol - C

How would this be done in a query?

I'm guessing 2 fields - 1 for Name from [EmployeeList] and the other field a chain of IIFs that look like this:

iif( [EmployeeList.Position] = "Employee", [JobKey.Grade], iif( [EmployeeList.Position] = "Supervisor", [JobKey.Grade], iif( [EmployeeList.Position] = "Manager", [JobKey.Grade] )))

However, where where it has [JobKey.Grade], it doesn't know what job grade to pick. I need it to pick a specific cell adjacent to the position in the job key title, but I have no idea how to do this. Any tips? Thanks

[pbaldy]

You shouldn't need a formula, nor do you want one. In your query, add the job key table and join the two tables on the related field.

[Steven Seagal]

I've tried this but in reality, I have 25 different 'codes' (not just job grade) to fit in the same matrix. The example I gave about was just a dummy for ease of explanation. When trying to link these queries together, the program just froze. Also, I have these job key tables in queries. WOuld it speed things up if I used tables instead?

[pbaldy]

And you want to code 25 options into a formula? The normalized approach is having them in a table and joining the tables in a query. I don't understand what you mean by having them in a query not a table. Can you post the db here?