Contest rank by class

[Rmynhier]

This query is ranked by [Caption Total](All classes) from qryPrelims but I
want it ranked only on [Caption Total] for [Class]=A

SELECT qryPrelims.[Prelim Order], qryPrelims.School, qryPrelims.[Metro
School], qryPrelims.Class, qryPrelims.Division, qryPrelims.PrelimMusicTotal,
qryPrelims.PrelimVisualTotal, qryPrelims.PrelimEffectTotal,
qryPrelims.[Points Deduction], qryPrelims.[Caption Total],
(SELECT Count(*) FROM qryPrelims AS Q WHERE qryPrelims.[Caption Total] <=
Q.[Caption Total]) AS Rank
FROM qryPrelims
WHERE (((qryPrelims.Class)="A"))
ORDER BY qryPrelims.[Caption Total] DESC;

 

[Marshall Barton]

This query is ranked by [Caption Total](All classes) from qryPrelims but I
want it ranked only on [Caption Total] for [Class]=A

SELECT qryPrelims.[Prelim Order], qryPrelims.School, qryPrelims.[Metro
School], qryPrelims.Class, qryPrelims.Division, qryPrelims.PrelimMusicTotal,
qryPrelims.PrelimVisualTotal, qryPrelims.PrelimEffectTotal,
qryPrelims.[Points Deduction], qryPrelims.[Caption Total],
(SELECT Count(*) FROM qryPrelims AS Q WHERE qryPrelims.[Caption Total] <=
Q.[Caption Total]) AS Rank
FROM qryPrelims
WHERE (((qryPrelims.Class)="A"))
ORDER BY qryPrelims.[Caption Total] DESC;

If you want to rank by class, then start with something more
like:

SELECT [Prelim Order], School, [Metro School],
Class, Division, PrelimMusicTotal,
PrelimVisualTotal, PrelimEffectTotal,
[Points Deduction], [Caption Total],
(SELECT Count(*)
FROM qryPrelims AS Q
WHERE Q.Class = qryPrelims.Class
And Q.[Caption Total] <= qryPrelims.[Caption Total]
) AS Rank
FROM qryPrelims
ORDER BY [Caption Total] DESC