Query to RANK employees

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!! Does anybody know if there is a way to RANK Employees in a query? I
have a table with cummulative sales by agent and I want to add a Field that
includes the rank... For exaple:

Employee - Sales - Rank
Jane - 1000 - 1
Robert - 950 - 2
Mike - 700 - 3
and so on...

Thanks for your help!!!
 
Is the rank determined by the cumulative sales? If so, why not simply sort on
cumulative sales? It is not generally good design to store a derivable
property in the table.

However, it is doable as:

select S.Employee, S.Cumulative,
(select count(*) from tblSales A
where A.Cumulative <= S.Cumulative) as RANK
from tblSales S
order by S.Cumulative;

The Query Grid will look exactly as the above:

Field: Employees Cumulative RANK: (the count query statement)
Sort: Ascending

Good Luck!
 
Back
Top