Contest rank by class

  • Thread starter Thread starter Rmynhier
  • Start date Start date
R

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;
 
Rmynhier said:
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;
Click to expand...

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
 
Back
Top