Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10198

Re: Assigning Position to students base on scores

Show all headers | View raw

On 22/12/2014 22:19:29, Patrick Finucane wrote:
> On Monday, December 22, 2014 4:01:41 PM UTC-5, Emmanuel Kumaku wrote:
>> I have database in Access  for keeping students' academic records. It con
> sist 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 su
> bject
>>
>> When more subjects are registered the formula assigned the rank without r
> egarding which subject they come from. I want to achieve this without
> creat ing separate queries for each subjects
>> I need help to be able to do the ranking base on the subjects. I have tri
> ed this but it is not working
>> POSITION: DCount("*","qryAssesment","[totalScore]>" & [totalScore] AND [s
> trsubjectName] =" & [strSubjectName])+ 1
> 
> I have no idea if this will help you out. I select the table I want and ru
> n a query. I display the ID and the relative position of the record. You
> could add a field to a table; maybe SortOrder. Then use my code below. Th
> en update SortOrder field with the relative position (should be cleared
> out first as it's just temp data). Then run your report sorted on
> SortOrder f ield.
> 
> Public Sub JunkIt()
> Dim rst As Recordset
> Set rst = CurrentDb.OpenRecordset("Select Test.* From Test Order By I
> D", dbOpenSnapshot)  'change to dbOpenDynaset if updating table
> rst.MoveFirst
> Do While Not rst.EOF
> Debug.Print rst!ID & " " rst.AbsolutePosition
> rst.MoveNext
> Loop
> rst.Close
> End Sub
> 

My gut feeling is a CrossTab query will do what you want with the subjects as
column headings and the students as row headings and the TotalScore as the
Value

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Assigning Position to students base on scores Emmanuel Kumaku <ekumaku@gmail.com> - 2014-12-22 13:01 -0800
  Re: Assigning Position to students base on scores Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-12-22 14:19 -0800
    Re: Assigning Position to students base on scores "Phil" <phil@stantonfamily.co.uk> - 2014-12-23 10:21 +0000
  Re: Assigning Position to students base on scores CDMAPoster@fortunejames.com - 2015-01-02 13:02 -0800

csiph-web