Speeding up a Ranking Formula

[TBC]

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.

[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]The complete code is:[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Option Compare Database[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim lngLastPoints As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim lngLastRank As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim lngRankInc As Long[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Function RankFunction(lngPoints As Long) As Long[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]lngLastPoints = 0[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]If lngLastPoints = lngPoints Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   RankFunction = lngLastRank[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    lngRankInc = lngRankInc + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    lngLastPoints = lngPoints[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     lngRankInc = lngRankInc + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     RankFunction = lngRankInc[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     lngLastRank = lngRankInc[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     lngLastPoints = lngPoints[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End Function[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]

 

[gemma-the-husky]

how big is your table/query, you are trying to sort.

stuff like this needs every row to be evaluated indivdiually - so in a large table this is likely to be a long process.

can you not just sort on the permanent score, and leave it at that. you can add a position on a report - as a report is naturally sequential, in a way that data is not.

 

[TBC]

There are anywhere between 500 records to 5000.
Do you have any recommendations?