Ranking among only certain records

  • Thread starter Thread starter max
  • Start date Start date
M

max

I have a query called student_marks with fields:
studentID,className averageScore,(among others)
The field className contains strings indicating the
respective class the student is in.
E.g:1blue,1red,1green,2blue,2red,...,4green...,and so no.
I would like to add 2 calculated fields to the query:

field1: The rank(position) of the student within his class
according to his average score.
i.e. select only those records where className="2blue" and
then return the rank of average according to this group.

field2:The rank(position) of the student within his year
mates (all those in a class beginning with a certain digit)
according to his average score.
i.e. select only those records where className like 1* and
then return the rank of average according to this group.

Is this possible without making any other parameter querys
to first find students in a certain class???

I am trying to expound on the idea in knowledge base
article Q120608 "How to Rank Records Within a Query"


Please help
 
SELECT *,
(SELECT Count(*)
FROM Student_Marks s
WHERE s.AverageScore <=Student_Marks.AverageScore
AND s.ClassName = Student_Marks.AverageScore) as ClassRank,
(SELECT Count(*)
FROM Student_Marks s
WHERE s.AverageScore <=Student_Marks.AverageScore
AND Left(s.ClassName,1) = Left(Student_Marks.AverageScore,1)) as
YearRank
FROM Student_Marks;

I think I have the "<=" correct. You may need to reverse.
 
I have a query called student_marks with fields:
studentID,className averageScore,(among others)
The field className contains strings indicating the
respective class the student is in.
E.g:1blue,1red,1green,2blue,2red,...,4green...,and so no.
I would like to add 2 calculated fields to the query:

field1: The rank(position) of the student within his class
according to his average score.
i.e. select only those records where className="2blue" and
then return the rank of average according to this group.

field2:The rank(position) of the student within his year
mates (all those in a class beginning with a certain digit)
according to his average score.
i.e. select only those records where className like 1* and
then return the rank of average according to this group.

Is this possible without making any other parameter querys
to first find students in a certain class???

I am trying to expound on the idea in knowledge base
article Q120608 "How to Rank Records Within a Query"
Click to expand...

You can use the ClassName in the criteria of DCount:

Field1: DCount("*", "[student_marks]", "[class] = '" & [class] & "'
AND AverageScore <= " & [AverageScore])
Field2: DCount("*", "[student_marks]", "[class] LIKE '" &
Left([class], 1) & "*' AND AverageScore <= " & [AverageScore])
 
Back
Top