Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Las Vegas
    Posts
    2

    Unanswered: Ranking Data In Fields Based on Unique ID

    I have figured out how to Rank my database based on a date, however I would like to Rank by each person's ID and then by Date.
    For example:
    Here is what I currently get

    PID ARR FirstOfFN FirstOfLN RANK
    10113094 6/14/2002 BILLY FOWLER 1
    10113094 6/6/2002 BILLY FOWLER 2
    3623042 6/4/2002 JAMES WARREN 3
    10113094 6/2/2002 BILLY FOWLER 4
    10113094 5/13/2002 BILLY FOWLER 5
    10113094 4/19/2002 BILLY FOWLER 6
    3623042 2/3/2002 JAMES WARREN 7
    3623042 1/30/2002 JAMES WARREN 8
    10113094 1/21/2002 BILLY FOWLER 9
    10113094 1/8/2002 BILLY FOWLER 10
    10113094 12/12/2001 BILLY FOWLER 11
    10113094 12/11/2001 BILLY FOWLER 12

    I would like to get this:

    PID ARR FirstOfFN FirstOfLN RANK
    3623042 6/4/2002 JAMES WARREN 1
    3623042 2/3/2002 JAMES WARREN 2
    3623042 1/30/2002 JAMES WARREN 3
    10113094 6/14/2002 BILLY FOWLER 1
    10113094 6/6/2002 BILLY FOWLER 2
    10113094 6/2/2002 BILLY FOWLER 3
    10113094 5/13/2002 BILLY FOWLER 4
    10113094 4/19/2002 BILLY FOWLER 5
    10113094 1/21/2002 BILLY FOWLER 6
    10113094 1/8/2002 BILLY FOWLER 7
    10113094 12/12/2001 BILLY FOWLER 8
    10113094 12/11/2001 BILLY FOWLER 9

    Any ideas?

  2. #2
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91
    If your PID-Date combination is unique, you can use:
    Rank: DCount("*","yourTableOrQueryName","PID = " & [PID] & " And ARR >= #" & [ARR] & "#")

    Here is my full SQL query generated by Access:
    SELECT yourTableOrQueryName.PID, yourTableOrQueryName.ARR, yourTableOrQueryName.FirstOfFN, yourTableOrQueryName.FirstOfLN, DCount("*","yourTableOrQueryName","PID = " & [PID] & " And ARR >= #" & [ARR] & "#") AS Rank
    FROM yourTableOrQueryName
    ORDER BY yourTableOrQueryName.PID, yourTableOrQueryName.ARR DESC;

  3. #3
    Join Date
    Dec 2002
    Location
    Las Vegas
    Posts
    2
    Djoko Rocks!

    So far it seems to work on the two examples, now I need to apply it to the 498,000 records!

    Looks as though I may not have to sleep in my office tonight!
    Thanks

    Barry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •