Speeding up a Ranking Formula

Does anyone know how I can speed up a Ranking query? I’m currently using the formula below:

Rank: (Select count(*) from qryCustomerTotals1 as B where qryCustomerTotals1.CustomerTotal > B.customerTotal)

From reading on the web, some people talk about doing it in a modules, they even give some examples (see below)

I’m just learning access, and have come a long way with all your help. I sure could use some advice with this one.

I don’t even one where to begin putting together the code below.

Thanks for taking the time to help a new access guy.

Code:

---

The complete code is:

Option Compare Database
Dim lngLastPoints As Long
Dim lngLastRank As Long
Dim lngRankInc As Long

Function RankFunction(lngPoints As Long) As Long

lngLastPoints = 0

If lngLastPoints = lngPoints Then
  RankFunction = lngLastRank
    lngRankInc = lngRankInc + 1
    lngLastPoints = lngPoints
Else
    lngRankInc = lngRankInc + 1
    RankFunction = lngRankInc
    lngLastRank = lngRankInc
    lngLastPoints = lngPoints
End If
End Function

---

Don't use Select *, use only the fields that you need.

Also it will be faster if your fields on the where side of the query are part of an index.

Post the query you have written for finding the rank, there will always room for fine tuning!!