If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ranking customer service metrics - Query issue
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. |
#2
|
|||
|
|||
Ranking customer service metrics - Query issue
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 "Nicholas Scarpinato" wrote: 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. |
#3
|
|||
|
|||
Ranking customer service metrics - Query issue
Hi -
Consider the Switch() function, e.g. n = [mymetric] ? switch(n=76, "EX", n=65, "EE", n=55, "ME", N55, "NI") Try modifying the example to meet your requirements. HTH - Bob Nicholas Scarpinato wrote: 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. -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Ranking customer service metrics - Query issue
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. "KARL DEWEY" wrote: 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 "Nicholas Scarpinato" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|