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?
Max
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?
Max
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> ... 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!
(°v°)
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!
(°v°)
ASKER
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!
MAX