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:

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:

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:

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

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

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]