Forum Discussion
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...
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.
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.
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