• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1204
  • 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!

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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