Complex ranking

[Les Isaacs]

Hi All
I have a query that uses a subquery to generate ranking: it is necessarily quite complex, because I need to rank teams of competitors across multiple races The underlying table [tblSavedResults] has separate records for each competitor for each race, and for each record there is a field [Athlete_clubteam_rank] (containing the rank of that competitor's team in that race), and a field [Athlete_clubteam_name] (containing the name of that competitor's team).
I currently have:

ClubRankSum: DSum("[tblSavedResults]![Athlete_clubteam_rank]","[tblSavedResults]","[tblSavedResults]![Athlete_clubteam_name]= """ & [tblSavedResultss]![Athlete_clubteam_name] & """ ")*1/IIf([Athlete_sex]="M",[RaceClubTeamSizeMale],[RaceClubTeamSizeFemale])

This almost gives me what I want - it gives me the 'score' for each team - i.e. the sum of that team's ranks across all the races (so if a team was ranked 1st, 3rd and 4th in the 3 races, ClubRankSum comes to 8. It would be much better, however, to actually rank these scores - so the team with the lowest score gets a rank of 1, etc etc.

I realise that I could do this by creating a 2nd query on my initial query, but would prefer if possible to do it in the initial query. Question is - can it be done?

Thanks for any help.
Les