• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1217
  • Last Modified:

Rank records in an Access Dbase by date

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, http://support.microsoft.com/kb/208946 (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?

  • 2
2 Solutions
Rey Obrero (Capricorn1)Commented:
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]
Example 1 is meant for a single ranking. You need to incorporate the [Customer Number] filter in your case. Something like this:

    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!
MaxwellTurnerAuthor Commented:
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!

> ... 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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now