Forum Discussion

DEB14439's avatar
DEB14439
Copper Contributor
Mar 22, 2022

Ordinal Rank in Query

Greetings:)

I am struggling with a query that will rank the 1st time a specific "award" is earned, 2nd time, and third or more times. I am an Access novice and can't quite figure it out. I'd like to run query that will give me this information. For a simplified example, Member1 earned his first service award and will receive a Blue Star at the next ceremony. Member2 earned a second service award and will receive a Red Star. (Member2 already received the Blue Star at a previous ceremony). Member3 earned his third award and will receive a Gold Star. From this point forward, third, fourth, fifth, etc. all receive Gold Star each time. If I can get this sorted out it will improve our process. We have many members and many unique awards.  Thank you if you have any ideas for me! -DEB

 

2016
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Mar 22, 2022
    As is almost always the case, we need to see the table design before offering any realistic suggestions. A screenshot of the DESIGN view of the table is optimal.

    You can find the "1st" time something happened by referring to the earliest date on which it happened, and that is done using Min(YourDateFieldNameGoesHere), but beyond that, we have to have more details to proceed without guessing.
    • DEB14439's avatar
      DEB14439
      Copper Contributor
      Mar 22, 2022

      George_Hepworth Thank you for your reply. As mentioned, I'm very much a novice. I've set up 3 tables - AwardsT, NomineesT and a JunctionTable to join them, many to many. I have a query for the Active Ceremony which gives me a list of the Nominees and the Awards they will receive at the next ceremony (AwardT Category = Active). I've tried to use Count but that doesn't work if Nominee1 is receiving both the first Award (Blue) and the second Award (Red) at the same ceremony as happens frequently. I can export to Excel and edit to get my results, but hoping to simply the process if it's possible. Maybe it's not. Thank you for your help! -DEB

       

Share

Resources