Query to RANK employees

[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!!!

 

[Guest]

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!