Speeding up a Ranking Formula

[TCB1]

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.

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

[dglienna]

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

[DataMiser]

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

[ComITSolutions]

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