A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ranking customer service metrics - Query issue



 
 
Thread Tools Display Modes
  #1  
Old June 25th, 2008, 10:26 PM posted to microsoft.public.access.queries
Nicholas Scarpinato
external usenet poster
 
Posts: 114
Default 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  
Old June 25th, 2008, 10:55 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old June 25th, 2008, 10:58 PM posted to microsoft.public.access.queries
raskew via AccessMonster.com
external usenet poster
 
Posts: 370
Default 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  
Old June 25th, 2008, 11:30 PM posted to microsoft.public.access.queries
Nicholas Scarpinato
external usenet poster
 
Posts: 114
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.