Running a Query to Establish Rank Based on Points Value

sstidham

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

 Seniority: (Select Count(*) from Employees Where [HireDate] < _ 

   [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

NeoPa

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)?

sstidham

The query we want the ranking to be in is:

Expand|Select|Wrap|Line Numbers

 SELECT [AGENT NAME],

       Sum([Occurance Score]+

           [Adherence]+

           [EQAMS]+

           [Survey Score]) AS [Total Points]

FROM Test3

GROUP BY [AGENT NAME];

The query it is pulling data from is:

Expand|Select|Wrap|Line Numbers

 SELECT [AGENT NAME],

       25-(25*[OCC]/10) AS [Occurance Score],

       [ADH]*20 AS Adherence,

       ([QASCORE]/100)*5 AS EQAMS,

       ([TOTLSC POST]/100)*25 AS [Survey Score]

FROM FlatFileData

GROUP BY [AGENT NAME],

         [OCC],

         [ADH],

         [QASCORE],

         [TOTLSC POST];

Hope this helps.

NeoPa

Try the following then. It produces a field using a sub-query (lines #6-8).

Expand|Select|Wrap|Line Numbers

 SELECT [AGENT NAME],

       Sum([Occurance Score]+

           [Adherence]+

           [EQAMS]+

           [Survey Score]) AS [Total Points],

       (SELECT Count(*)

        FROM Test3 AS iT3

        WHERE iT3.[AGENT NAME]=oT3.[AGENT NAME])+1 AS [Pos]

FROM Test3 AS oT3

GROUP BY [AGENT NAME]

sstidham

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

NeoPa

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).

NeoPa

As before, but the "=" from line #8 is replaced by a "<".

Expand|Select|Wrap|Line Numbers

 SELECT [AGENT NAME],

       Sum([Occurance Score]+

           [Adherence]+

           [EQAMS]+

           [Survey Score]) AS [Total Points],

       (SELECT Count(*)

        FROM Test3 AS iT3

        WHERE iT3.[AGENT NAME]<oT3.[AGENT NAME])+1 AS [Pos]

FROM Test3 AS oT3

GROUP BY [AGENT NAME]

Running a Query to Establish Rank Based on Points Value

Post your reply

Similar topics