access 分组序号,使用Access SQL进行分组排名

How do I rank salespeople by # customers grouped by department (with ties included)?

For example, given this table, I want to create the Rank column on the right. How should I do this in Access?SalesPerson Dept #Customers Rank

Bill DeptA 20 1

Ted DeptA 30 2

Jane DeptA 40 3

Bill DeptB 50 1

Mary DeptB 60 2

I already know how to do a simple ranking with this SQL code. But I don't know how to rework this to accept grouping.

Select Count(*) from [Tbl] Where [#Customers] < [Tblx]![#Customers] )+1

Also, there's plenty of answers for this using SQL Server's Rank() function, but I need to do this in Access. Suggestions, please?

解决方案SELECT *, (select count(*) from tbl as tbl2 where

tbl.customers > tbl2.customers and tbl.dept = tbl2.dept) + 1 as rank from tbl

Just add the dept field to the subquery...