Assigning Position to students base on scores

Giganews Newsgroups
Subject: Assigning Position to students base on scores
Posted by:  Emmanuel Kumaku (ekuma…@gmail.com)
Date: Mon, 22 Dec 2014

I have database in Access  for keeping students' academic records. It consist of three tables
tblStudentInfor, tblSubjects and tblRegisteredSubjects
the tblRegisteredSubjects consist of the following fields
strSubjectId (a lookup from the tblSubjects table)
strStudentId (a lookup from the tblStudentsInfor table)
AssignmentScore
ExamScore

I have a query (qryAssessment) that have the following fields
strSubjectName (from the tblSubjects table)
strStudentsName(from the tblStudentInfor table)
AssingmentScore( from tblRegisteredSubjects table)
ExamScore (from tblRegisteredSubjects table)
TotalScore( a calculated field ie AssignmentScore + ExamScore)

Position (Expression i.e *","qryAssesment","[totalScore]>" & [totalScore])+ 1

The position rank the students according to their scores

The position field works perfectly when all students register only one subject

When more subjects are registered the formula assigned the rank without regarding which subject they come from. I want to achieve this without creating separate queries for each subjects
I need help to be able to do the ranking base on the subjects. I have tried this but it is not working
POSITION: DCount("*","qryAssesment","[totalScore]>" & [totalScore] AND [strsubjectName] =" & [strSubjectName])+ 1

Replies