Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    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.
    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
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •