Return Highest Score

[thethack]

I am recording scores for each of several attempts per participant into a Data Table and want a query to yield their best and second best scores from all attempts and then rank the participants based on their best scores.

As one participant's best attempt may be their third try and another particpant's best attempt their second try and so on, it is likely many participants will have their best attempts in different rounds. However, I need to compare their single best attempt against the best attempt of the other participants, regardless of round, and in the event of a tie their second best attempt and so on to rank the order or place for each participant. Is there a way to get this done with a query? Could the query then rank the order of finish?

 

[Rich]

Search here for Ranking or rank, there have been several examples posted including on tying, which should help you.

 

[thethack]

Thanks Rich. I did the search you referenced and found where you had recommended dowloading the qrysmp from Micrsoft. I have done that and I think I can do the ranking of the participants once I get the best scores in the same field. I had hoped to come up with a query that would look at all the scores of a single participant (Attempt 1, Attempt 2, Attempt 3, et...) and place their highest score in a column called Best Attempt then rank on the best attempt. Not sure if that sample query I downloaded has that kind of example in it.

I do appreciate your help!

 

[Jon K]

"... and in the event of a tie their second best attempt and so on to rank the order ..."
"... Not sure if that sample query I downloaded has that kind of example in it. "

The sample queries in the download can help you rank the best attempt, but not the second best attempt when there is a tie.

 

[thethack]

Thanks Jon, I was finding that to be the case in the example query I downloaded. I guess I will have to manually do something at this point. I appreciate your input.

 

[The_Doc_Man]

Your problem (from the sound of it) is that you have flattened a table just a bit too much. Access is relational. You are using a spreadsheet viewpoint on your tries.

You should have a contestant table, an event table, and a tries table.

Contestant =
ContID (maybe autonumber)
ContFirstName, ContLastName, etc etc etc

Event =
EventID (maybe autonumber)
EventName, other event info

Tries =
ContID
EventID
TryNumber (1, 2, 3, 4, however many are allowed)
Result
SelfOrdinal

Now, do an update query that updates field SelfOrdinal =
DCount( "[Result]", "Tries", "[ContID] = " & CStr( [ContID] ) & " AND [EventID] = " & CStr( [EventID] ) & " AND [Result] > " & [Result] ) + 1

(This assumes that CondID, EventID, and Result are all numeric.) The count is the number of tries better than the try in question FOR THAT USER FOR THAT EVENT.

Now, after the update, for any given contestant, the best tries list is obtained by making a query based on the SelfOrdinal = 1 and the current event ID

To compare the tries of two persons for the same event, you have to get trickier. Such as another field or two in the tries table. Maybe ContOrdinal1 and ContOrdinal2

where again you update based on a Dcount query, this time for the same event and ContOrdinal1 and selecting for SelfOrdinal = 1, but generate the ordinal numbers across contestants. I.e. count how many contestants have lesser scores.

Do the same thing for ContOrdinal2 and SelfOrdinal = 2.

Now, your tie-breaker is just a query across all contestants for the same event, ordered by ContOrdinal1, with ContOrdinal2 as the second sort (the tie-breaker).

In English: For each contestant and event, assign an ordinal number that shows the best, second best, third best, etc. attempt. This is the SelfOrdinal.

Now, for a given event, using only the SelfOrdinal for the best result (=1), again count the number of contestants whose results better than this one. The one with the best score is 1, and this score goes into what I called ContOrdinal1.

Do the same thing for SelfOrdinal of 2 for a given event to find the contestants with second-best tries better than this one. The best second try has a score of 1, and this goes into ContOrdinal2.

The ONLY problem you are going to have is what to do if one contestant scores the same result on two tries. In that initial query that determines SelfOrdinal, you might have to use a secondary condition to break ties within an individual's Tries list.

Hope that didn't lose you. It is SOMEWHAT akin to the way Olympic Skating is judged. Not exactly, but somewhat. (Actually, it is closer to the way Duplicate Bridge is scored when playing match points.)

 

[thethack]

Thanks Doc.... that looks like it may very well solve the overall concern. I will have to give it a try. I appreciate your taking the time to be so thorough in your reply.