Ranking customer service metrics - Query issue

[Nicholas Scarpinato]

I'm building a report to rank a series of customer service metrics, such as
call time and calls per hour. I have all the data compiled, but the person
I'm building this report for wants to have a "grade" for each metric. They've
given me the criteria for each grade in each metric, but for the life of me I
can't figure out how to make it work. I know it's probably a simple thing
I've overlooked, but I've been working on this for a while now and nothing
I've tried has worked. (I was having an issue with averages, but I finally
worked through that... so this is the last piece of the puzzle before I can
finally get this reporting database finished.)

Here's what I have so far as an example. I have a table, called
tblMetricsRanking, that has four fields:

Metric
Low %
High %
Ranking

The breakdown of the actual scores varies by metric, but they're all graded
on the same scale: EX, EE, ME, NI, with EE being the top and NI being the
bottom. (EX = Excellent, EE = Exceeds Expectations, ME = Meets Expectations,
and NI = Needs Improvement.) So, if the scale for EX is 76 to 100, and the
person's score was 89, they would fall into that bracket. Maybe I'm just
overthinking my problem or something, but I'm stuck here. I've also tried
this using only the low scores (I should only need one number to do this, I
would think), but I couldn't make that work either. Any ideas are
appreciated. Thank you.

 

[KARL DEWEY]

You are on the right track.
Your translation table has one more field than below --
Metric Low % High % Ranking

MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

Using the same technique join the two tables on METRIC.

 

[Nicholas Scarpinato]

That worked beautifully, thank you. I knew I was close, but putting it in SQL
form helped me figure out what I was missing. It needed a slight modification
to make it work for my table, but now everything works perfectly.

You are on the right track.
Your translation table has one more field than below --
Metric Low % High % Ranking

MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

Using the same technique join the two tables on METRIC.
--
KARL DEWEY
Build a little - Test a little

I'm building a report to rank a series of customer service metrics, such as
call time and calls per hour. I have all the data compiled, but the person
I'm building this report for wants to have a "grade" for each metric. They've
given me the criteria for each grade in each metric, but for the life of me I
can't figure out how to make it work. I know it's probably a simple thing
I've overlooked, but I've been working on this for a while now and nothing
I've tried has worked. (I was having an issue with averages, but I finally
worked through that... so this is the last piece of the puzzle before I can
finally get this reporting database finished.)

Here's what I have so far as an example. I have a table, called
tblMetricsRanking, that has four fields:

Metric
Low %
High %
Ranking

The breakdown of the actual scores varies by metric, but they're all graded
on the same scale: EX, EE, ME, NI, with EE being the top and NI being the
bottom. (EX = Excellent, EE = Exceeds Expectations, ME = Meets Expectations,
and NI = Needs Improvement.) So, if the scale for EX is 76 to 100, and the
person's score was 89, they would fall into that bracket. Maybe I'm just
overthinking my problem or something, but I'm stuck here. I've also tried
this using only the low scores (I should only need one number to do this, I
would think), but I couldn't make that work either. Any ideas are
appreciated. Thank you.