By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

Running a Query to Establish Rank Based on Points Value

P: 11
I have a query which calculates the data from two table and then yields the number of points based on various calculations for each category.

Essentially we are trying to rank our Agents based on performance, and each category (Senority, Sales, occurances etc) has a points value. We have a final query that combines all these points values for the different categories to yeild the final total.

What I need is a query that will show the rank of the Agent based on this points value, but gives the same value if a tie is in place.

I have Agent Name as one field in the query and Total Points as the other. I would need a third field Rank, and the only thing I could find was:
Expand|Select|Wrap|Line Numbers
  1. Seniority: (Select Count(*) from Employees Where [HireDate] < _ 
  2.    [Emp1].[HireDate]+1;)
Which is an example given on Microsoft help, but I played with it and still have no idea what to change to give the correct result, it gives me the Syntax error. I was hoping someone could point me in the right direction.

Thanks.
Stephanie
May 6 '08 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,299
What is the data you're trying to work with?
Which fields do you have?
Is the recordset you're working from a table or a query (If query, please post the query's SQL)?
May 7 '08 #2

P: 11
The query we want the ranking to be in is:
Expand|Select|Wrap|Line Numbers
  1. SELECT [AGENT NAME],
  2.        Sum([Occurance Score]+
  3.            [Adherence]+
  4.            [EQAMS]+
  5.            [Survey Score]) AS [Total Points]
  6. FROM Test3
  7. GROUP BY [AGENT NAME];
The query it is pulling data from is:
Expand|Select|Wrap|Line Numbers
  1. SELECT [AGENT NAME],
  2.        25-(25*[OCC]/10) AS [Occurance Score],
  3.        [ADH]*20 AS Adherence,
  4.        ([QASCORE]/100)*5 AS EQAMS,
  5.        ([TOTLSC POST]/100)*25 AS [Survey Score]
  6. FROM FlatFileData
  7. GROUP BY [AGENT NAME],
  8.          [OCC],
  9.          [ADH],
  10.          [QASCORE],
  11.          [TOTLSC POST];
Hope this helps.
May 8 '08 #3

NeoPa
Expert Mod 15k+
P: 31,299
Try the following then. It produces a field using a sub-query (lines #6-8).
Expand|Select|Wrap|Line Numbers
  1. SELECT [AGENT NAME],
  2.        Sum([Occurance Score]+
  3.            [Adherence]+
  4.            [EQAMS]+
  5.            [Survey Score]) AS [Total Points],
  6.        (SELECT Count(*)
  7.         FROM Test3 AS iT3
  8.         WHERE iT3.[AGENT NAME]=oT3.[AGENT NAME])+1 AS [Pos]
  9. FROM Test3 AS oT3
  10. GROUP BY [AGENT NAME]
May 8 '08 #4

P: 11
That seems to be working, however, it is giving everyone the same rank even though the totals value is different. Everyone is getting rank 2 if I leave the +1 and then if I remove it everyone is getting plus 1.

Thank you so much. I hate to be such a bother
May 17 '08 #5

NeoPa
Expert Mod 15k+
P: 31,299
That'll be because I SNAFUed. Sorry (and it's no bother - I'd far rather you told me than leave any wrong code up - ESPECIALLY if it's mine).
May 19 '08 #6

NeoPa
Expert Mod 15k+
P: 31,299
As before, but the "=" from line #8 is replaced by a "<".
Expand|Select|Wrap|Line Numbers
  1. SELECT [AGENT NAME],
  2.        Sum([Occurance Score]+
  3.            [Adherence]+
  4.            [EQAMS]+
  5.            [Survey Score]) AS [Total Points],
  6.        (SELECT Count(*)
  7.         FROM Test3 AS iT3
  8.         WHERE iT3.[AGENT NAME]<oT3.[AGENT NAME])+1 AS [Pos]
  9. FROM Test3 AS oT3
  10. GROUP BY [AGENT NAME]
May 19 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.