Complex compare in MS Access

I have to write a db to help with a youth competition day. One of the competitions is First Aid, where a team of four run through a scenario. Each organisation can only enter one team, so 1 record = 1 team entry.

The scoring:
Each team member is scored on their individual performance, while the Team is given a score for how effectively they work together. All 5 scores are added together to give an overall score.
To save time in the ranking process, I decided to store all 6 values against the organisation name in a table called "tblFirstAidYouth". (I also have a tblFirstAidAdult.)
The value names are: FAY01Name; FAY01Score; FAY01Name; FAY02Score; FAY03Name; FAY03Score; FAY04Name; FAY04Score; FAYTeamScore; FAYOverallScore.

qryFAY then sorts the overall scores to give a ranked finishing order.

The competitor names are taken from another table rather than manually entered. This is because a second db is checked to list people within the organisation who are under 16 on the day of the competition. The output of that is entered into my db as tblFAYCompetitors. Rather than storing a name, the table stores the unique ID from tblFAYCompetitors. Not really an issue, but in my mind adds another step or query into the process.

I've been asked to work out which 'individual' team member scored highest, then produce a report detailing Name, Score and Organisation.

How do I compare the four individual scores within each record, with every other record to find the single highest value or multiple in the case of a tie?

I have a single query which returns details of all four team members.

I think I need to identify and return the maximum score per team, then compare that with the maximum scores for every other team.

Where this falls down is if two members from a single team have the same score, or if two members from the same team have the two highest scores (hence overall finish 1st and 2nd).

This is what I have so far:
qryFAY

SELECT 
   tblFirstAidYouth.ID, tblFirstAidYouth.CompetitionID, 
   tblFirstAidYouth.FAYOrgID, tblFirstAidYouth.FAYTeam, 
   tblFirstAidYouth.FAY01Name, tblFirstAidYouth.FAY01Score, 
   tblFirstAidYouth.FAY02Name, tblFirstAidYouth.FAY02Score, 
   tblFirstAidYouth.FAY03Name, tblFirstAidYouth.FAY03Score, 
   tblFirstAidYouth.FAY04Name, tblFirstAidYouth.FAY04Score, 
   tblFirstAidYouth.FAYOverallScore, tblOrgs.OrgNo, tblOrgs.OrgName
FROM 
   tblOrgs 
RIGHT JOIN 
   tblFirstAidYouth ON tblOrgs.OrgID = tblFirstAidYouth.FAYOrgID;

How can I compare each score with every other score to return the highest value?

Solution

Consider a union query to stack all scores together. (Ideally, this is not a query but how you actually store your data where you keep data long for scalability and efficiency).

SELECT 
   f.ID, f.CompetitionID, 
   f.FAYOrgID, f.FAYTeam, 
   f.FAY01Name AS FAYName, f.FAY01Score AS FAYScore, 
   o.OrgNo, o.OrgName,
   f.FAYOverallScore
FROM tblFirstAidYouth f
LEFT JOIN tblOrgs o
   ON o.OrgID = f.FAYOrgID

UNION ALL

SELECT 
   f.ID, f.CompetitionID, 
   f.FAYOrgID, f.FAYTeam, 
   f.FAY02Name, f.FAY02Score, 
   o.OrgNo, o.OrgName,
   f.FAYOverallScore
FROM tblFirstAidYouth f
LEFT JOIN tblOrgs o
   ON o.OrgID = f.FAYOrgID

UNION ALL

SELECT 
   f.ID, f.CompetitionID, 
   f.FAYOrgID, f.FAYTeam, 
   f.FAY03Name, f.FAY03Score, 
   o.OrgNo, o.OrgName,
   f.FAYOverallScore
FROM tblFirstAidYouth f
LEFT JOIN tblOrgs o
   ON o.OrgID = f.FAYOrgID

UNION ALL

SELECT 
   f.ID, f.CompetitionID, 
   f.FAYOrgID, f.FAYTeam, 
   f.FAY04Name, f.FAY04Score, 
   o.OrgNo, o.OrgName,
   f.FAYOverallScore
FROM tblFirstAidYouth f
LEFT JOIN tblOrgs o
   ON o.OrgID = f.FAYOrgID

From there, derive a row number to rank order all participants scores. Hopefully soon, MS Access SQL will support window functions like other DBMS's. Until then, a correlated DCount or count aggregate can work. But it may be more efficient to save your union query in a new table (which as mentioned above should be your original data source structure).

SELECT * INTO myScoresTable FROM myUnionQuery

Below runs a within team and overall score rankings for the special case that the two highest may be on same team. You can do same for overall team score.

SELECT 
   s.ID, s.CompetitionID, 
   s.FAYOrgID, s.FAYTeam, 
   s.FAYName, s.FAYScore, 
   s.OrgNo, s.OrgName,
   s.FAYOverallScore,

   (SELECT COUNT(*) 
    FROM myScoresTable sub_s
    WHERE
       sub_s.OrgNo = s.OrgNo AND
       sub_s.FAYScore >= s.FAYScore
   ) AS FAYScoreWithinTeamRank,

   (SELECT COUNT(*) 
    FROM myScoresTable sub_s
    WHERE sub_s.FAYScore >= s.FAYScore
   ) AS FAYScoreRank

FROM myScoresTable s

Final Query to select highest scored individual or more for ties. If the top scorers come from same team, manually adjust the rank filter for next top scorers e.g.,FAYScoreRank <= 2. This gets complex to automate in SQL. You could nest above in below as a derived table.

SELECT *
FROM myRankScoreQuery
WHERE FAYScoreRank = 1