Page 1 of 4 123 ... LastLast
Results 1 to 15 of 50

Thread: Rank Results

  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have a query which uses an expression to calculate a [score]. The score is based on adding and multiplying other fields, and the end scores are from 35 to 250. I am trying to establish a system to rank (in a report) the results by the score. That I can do. But I need the ability to assigning a ranking number to the score. The highest score would be [ranking] 1, the next highest is [ranking] 2, and so forth. The problem I haven't been able to conquer is to how to reset the [ranking] results in the proper order, when the [score] changes. I was thinking of an update query, but then I also thought about using a 'make-table' query, where I could use an "autonumber" field for the ranking number, and then each time the scores would change, run the code, using the code to delete the [autonumberID] field, and then create a new one {autonumber} field, since the autonumbering would be in order (based on query sending the results to the table in descending order).

    Not sure if this will work, so far I am stumbling on how to delete the autonumber field, and then create a new one after the table is regenerated.

    Does this sound doable?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You might consider using a report with the sequence number and that would do the job automatically when you sort by the score.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I wouldn't use an AutoNumber field. You can calculate the ranking in a query.

    Let's say that the query in which you calculate the scores is named qryScores, and that the field containing the score is imaginatively named Score.

    Create a new query based on qryScores.
    Add all fields, including Score (or *) to the query grid.
    In the first empty column, enter a calculated field:

    Rank: DCount("*","qryScores","Score>=" & [Score])

  4. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Wendell and Hans. I will look at both of these options. I'm sure one, if not both, will give me the results I am looking for. Cheers!

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have a follow up on my earlier dilema. I am in need of narrowing down my results, based on the [score] field, to the top 5 scores; however "tie" scores for 5th place must also be included. I know that setting the "Output All Fields" to 5 will return the top 5, but I also need to include those instances where the top 5 may actually be 6, 7, or 8 records, due to a tie for 5th postion, or even a tie for 3rd or 4th positions, if there are multiple results for those as well. Hopefully not too confusing...

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think you would need to add an extra step (or 2) in the process.
    1. First have a top 5 query.
    2. Then a second query that retrieves records when the score is one of the values in the top 5.

    You might need a third step.
    1. Find the top 5
    2. Find the minimum score from within the top 5
    3. find any scores greater than or equal to that minimum.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sounds a little complicated, but do-able. I will see if I can do what you have suggested. Thank you.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Slinky View Post
    I wouldn't use an AutoNumber field. You can calculate the ranking in a query.

    Let's say that the query in which you calculate the scores is named qryScores, and that the field containing the score is imaginatively named Score.

    Create a new query based on qryScores.
    Add all fields, including Score (or *) to the query grid.
    In the first empty column, enter a calculated field:

    Rank: DCount("*","qryScores","Score>=" & [Score])

    Slinky, the above code ranks the [score] in the correct order, but the sequence is not returning appropriately. For example, if I have 6 scores, and the last (2) are the same, the ranking does not show them a both at (ranking level) 5. The ranking is done as such:

    1
    2
    3
    4
    6
    6

    Is there any method to get the "ties" to be shown at the highest level, in the case able, marked as "5"? The ranking number will be feed to some reports, and will create confusion in its currect format.

    Thanks.

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at this from Microsoft Regarding Ranking in queries

    http://support.microsoft.com/kb/120608
    Andrew

  10. #10
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Andrew. I think this article will provide me the direction needed.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Andrew. I think this article will provide me the direction needed.

  12. #12
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Andrew. I think this article will provide me the direction needed.

  13. #13
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Andrew. I think this article will provide me the direction needed.

  14. #14
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Andrew. I think this article will provide me the direction needed.

  15. #15
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Andrew. I think this article will provide me the direction needed.

Page 1 of 4 123 ... LastLast

Posting Permissions

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