Results 1 to 2 of 2
Thread: Speeding up a Ranking Formula
2010-12-16, 15:37 #1
- Join Date
- Dec 2010
- Thanked 0 Times in 0 Posts
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
2010-12-16, 16:15 #2
- Join Date
- Feb 2001
- Silicon Valley, USA
- Thanked 94 Times in 90 Posts
I'm going to move this thread to the Access board where the database gurus spend most of their time.
Edit: I see you already are familiar with this board.