Ordinal Rank in Query

[DEB14439]

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

[#2]

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.

[#3]

@DEB14439 

You might also be able to adapt the approach Allen Browne describes here for ranking and numbering records. It's not precisely the same task, but close enough for you to get a few ideas about how to modify it to suit your design.

[#4]

@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

 

[#5]

Thank you, I will look at this link! -DEB

[#6]

@DEB14439 

I use a different approach to ranking using a Serialize function.
See my article: https://www.isladogs.co.uk/rank-order-queries/index.html

[#7]

I will see what I can do to figure this out. Thank you! -DEB