Rank records in an Access Dbase by date

Posted on 2007-10-04
Last Modified: 2010-05-18
I am trying to rank my customer orders dbase - a field which shows each cutomer's 1rst order, their 2nd order, 3rd order and so on, based on the invoice dates (sorted asc).  My table has 2 fields: Customer Number, Invoice Date.

Using an example shown here, (example 1), I am able to rank ONE customer at a time (If I filter my source table to show only one specific Customer Number).  However, my source table has 25000+ records with many Customer Numbers, each with between 1 and 100 Invoice Dates . . . when I try it unfiltered, it does not rank 1,2,3,4 . . . but rather something like 10, 47, 124 . . . .

Is it possible to rank all 25000+ records so that it will rank each Customer Number order  1,2,3,4 . . . and it will start over at 1 each time the Customer Number changes?

Question by:MaxwellTurner
    LVL 119

    Accepted Solution

    you will need a function to do that. place this function in a module

    Function GetCount(sFieldName As String) As Long
    Static sCurrentField As String
    Static intCnt As Long
        If sFieldName = sCurrentField Then
           intCnt = intCnt + 1
           intCnt = 1
           sCurrentField = sFieldName
        End If
    GetCount = intCnt
    End Function

    then use the is query

    select  [Customer Number], [Invoice Date], GetCount([Customer Number]) AS RowCount
    from orders
    order by [Customer Number], [Invoice Date]
    LVL 58

    Assisted Solution

    Example 1 is meant for a single ranking. You need to incorporate the [Customer Number] filter in your case. Something like this:

    SELECT YT.*,
        Select Count(*) From [Your Table]
        Where [Customer Number] = YT.[Customer Number]
          And [Invoice Date] < YT.[Invoice Date]
      )+1 AS Rank
    FROM [Your Table] AS YT
    ORDER BY YT.[Customer Number], YT.[Invoice Date];

    It would look much better with simpler field names, though.

    Have fun!
    LVL 1

    Author Comment

    Wow, thanks alot!  Both methods work great.  

    capricorn1: for some reason the ranking came out as 2,4,6,8 . . . . that was easily solved with:
    select  [Customer Number], [Invoice Date], (GetCount([Customer Number]) )/2 AS RowCount

    harfang: I almost had it . . . I was just missing the "[Customer Number] = YT.[Customer Number]
          And".  I felt a little stupid when I saw your solution . . . so simple!:)

    I will split the points between you guys - capricorn, solution & harfang, assisted solution because capricorn posted first.  Hope that sounds fair.

    Thanks again!  You guys are GOLD!

    LVL 58

    Expert Comment

    > ... so simple!

    Yes, that's often the case, don't worry about it. That's the entire value of an extra pair of eyes whenever you get stuck, right?

    Thanks and success with your project!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now