We help IT Professionals succeed at work.

Rank records in an Access Dbase by date

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, 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?

Watch Question

Top Expert 2016
Unlock this solution and get a sample of our free trial.
(No credit card required)
Unlock this solution and get a sample of our free trial.
(No credit card required)


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!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.