Use Access SQL to do a grouped ranking

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

I know this is an old thread. But since I spent a great deal of time on a very similar problem and was greatly helped by the former answers given here, I would like to share what I have found to be a MUCH faster way. (Beware, it is more complicated.)

First make another table called "Individualizer". This will have one field containing a list of numbers 1 through the-highest-rank-that-you-need.

Next create a VBA module and paste this into it:

'Global Declarations Section.
Option Explicit
Global Cntr

'*************************************************************
' Function:  Qcntr()
'
' Purpose: This function will increment and return a dynamic
' counter. This function should be called from a query.
'*************************************************************

Function QCntr(x) As Long
   Cntr = Cntr + 1
   QCntr = Cntr
End Function

'**************************************************************
' Function:  SetToZero()
'
' Purpose: This function will reset the global Cntr to 0. This
' function should be called each time before running a query
' containing the Qcntr() function.
'**************************************************************

Function SetToZero()
   Cntr = 0
End Function

Save it as Module1.

Next, create Query1 like this:

SELECT Table1.Dept, Count(Table1.Salesperson) AS CountOfSalesperson
FROM Table1
GROUP BY Table1.Dept;

Create a MakeTable query called Query2 like this:

SELECT SetToZero() AS Expr1, QCntr([ID]) AS Rank, Query1.Dept, 
Query1.CountOfSalesperson, Individualizer.ID 
INTO Qtable1
FROM Query1 
INNER JOIN Individualizer 
   ON Query1.CountOfSalesperson >= Individualizer.ID;

Create another MakeTable query called Query3 like this:

SELECT SetToZero() AS Expr1, QCntr([Identifier]) AS Rank, 
[Salesperson] & [Dept] & [#Customers] AS Identifier, Table1.Salesperson, 
Table1.Dept, Table1.[#Customers] 
INTO Qtable2
FROM Table1;

If you have another field already that uniquely identifies every row you wouldn't need to create an Identifier field.

Run Query2 and Query3 to create the tables. Create a fourth query called Query4 like this:

SELECT Qtable2.Salesperson, Qtable2.Dept, Qtable2.[#Customers], Qtable1.ID AS Rank
FROM Qtable1 
INNER JOIN Qtable2 ON Qtable1.Rank = Qtable2.Rank;

Query4 returns the result you are looking for.

Practically, you would want to write a VBA function to run Query2 and Query3 and then call that function from a button placed in a convenient location.

Now I know this sounds ridiculously complicated for the example you gave. But in real life, I am sure your table is more complicated than this. Hopefully my examples can be applied to your actual situation. In my database with over 12,000 records this method is by FAR the fastest (as in: 6 seconds with 12,000 records compared to over 1 minute with 262 records ranked with the subquery method).

The real secret for me was the MakeTable query because this ranking method is useless unless you immediately output the results to a table. But, this does limit the situations that it can be applied to.

P.S. I forgot to mention that in my database I was not pulling results directly from a table. The records had already gone through a string of queries and multiple calculations before they needed to be ranked. This probably contributed greatly to the huge difference in speed between the two methods in my situation. If you are pulling records directly from a table, you might not notice nearly as big an improvement.

Great solution with subquery! Except for huge recordsets, the subquery solution gets very slow. Its better(quicker) to use a Self JOIN, look at the folowing solution: self join

SELECT tbl1.SalesPerson , count(*) AS Rank 
FROM tbl AS tbl1 INNER JOIN tbl AS tbl2 ON tbl1.DEPT = tbl2.DEPT 
    AND tbl1.#Customers < tbl2.#Customers 
GROUP BY tbl1.SalesPerson