Maybe IIF, I don't know!!!!!

[Presto731]

I have a table that monitors employees performance on 10 different categories. For each category they receive a percentage. Each range of percentages is assigned a number.

For example. If for category 1 they have 85% their score would be x. So 0-19% is one pint, 20-29% is 2 points, so on down the line to 100%. each category has different ranges and point values.

I have never had to write such large IIF statement for each column of a query. Not even sure if thats the route I need to take.

Suffice it to say I want to have a query off this table that spits out each employess point total for each category.

I will take any and all suggestions. let me know if you need more info.

Please help, I have deadlines to meet :D

Thannk you in advance.

[jaxjagfan]

I have a table that monitors employees performance on 10 different categories. For each category they receive a percentage. Each range of percentages is assigned a number.

For example. If for category 1 they have 85% their score would be x. So 0-19% is one pint, 20-29% is 2 points, so on down the line to 100%. each category has different ranges and point values.

I have never had to write such large IIF statement for each column of a query. Not even sure if thats the route I need to take.

Suffice it to say I want to have a query off this table that spits out each employess point total for each category.

I will take any and all suggestions. let me know if you need more info.

Please help, I have deadlines to meet :D

Thannk you in advance.

Are all 10 categories evenly weighted? Does each count for 10% of the total possible? It would not hurt to add a "Weight" column to your "categories" table.
When the employee receives a score for a category divide the score by the category weight.

If an employee received an 85 for category1 and category1 weight is 10 then employee score would be 8.5 (85/10). You could adjust weights (some may be more important than others) but just make sure the total weight equals 100.

I think you were making it harder than it needs to be.

[Presto731]

The categories are weighted and are reflected in the amount of points they get within each range, each percentage range is also different for each category.

[jaxjagfan]

The categories are weighted and are reflected in the amount of points they get within each range, each percentage range is also different for each category.

If you divide score by weight you don't need a range.

85 would fall into your range of 80-90. 8 or 9 points depending on you set that range. My method gives them a score of 8.5. You can use the Round function to round the score to the nearest whole number.

Using your method you would need a points table with CategoryID, RngLow, RngHigh, Points columns. (This would be easier than maintaining a bunch of "IIF" statements in a query). You would need to write your query to lookup points based on something similar to this:

Select Points
From "yourjoined tables"
Where EmployeeScore Between RngLow and RndHigh AND EmpCategoryID = CategoryID