Dcount Function

[felixkiprono402@gmail.com]

Hello.
I have an ms Access 2010 Students database where I want to rank students based on semester and class. In my query I have [AdmNo],[TotamMarks], [Class], [Semester].
I used the the function =DCOUNT("*","qryRank","TotalMarks>"[TotalMarks])+1. It works perfectly.


But i want to add more criteria so that the rank is based on Class and Semester. So, how can i add the two criteria in the function above?

[andy49]

Something like?

DCOUNT("*","qryRank","TotalMarks>" & [TotalMarks]+1, & " and [class] = '" & txtclassname & "')"

Assuming the classname to search for will be from a textbox called txtclassname

[felixkiprono402@gmail.com]

Thanks Andy.
the thing is; I want it do a grouped ranking (i.e. Same Class and Same Semester ranked together)
Like:
=Dcount("*", "qryRank", "TotalMarks>"&[TotalMarks] WHERE [ClassID] is same AND [Semester] is also same)
something like that to return students list as below
ADM NAME CLASS MARKS RANK SEMESTER
100 JOHN FORM 1 600 1 TERM 1
200 JANE FORM 1 500 2 TERM 1
300 JOEL FORM 1 400 3 TERM 1
400 JOYCE FORM 2 500 1 TERM 1
500 JEAN FORM 2 100 2 TERM 1
100 JOHN FORM 1 700 1 TERM 2
200 JANE FORM 1 600 2 TERM 2
300 JOEL FORM 1 200 3 TERM 2
400 JOYCE FORM 2 500 1 TERM 2
500 JEAN FORM 2 100 2 TERM 2

[andy49]

Decount returns one value. You seem to be asking for the results of a query.

[felixkiprono402@gmail.com]

Andy, that's exactly what i am looking for. The value returned is RANK based on TotalMarks, Class and Semester at the same time. Please help me out of this. i want it in a way that it ranks students per class per semester according to marks scored by each. =DCount("*", "qryResults","TotalMarks>"&[TotalMarks] AND [Semester] is common AND [Class] is common)+ really waiting for any immediate help. thank you all

[felixkiprono402@gmail.com]

Andy, that's exactly what i am looking for. The value returned is RANK based on TotalMarks, Class and Semester at the same time. Please help me out of this. i want it in a way that it ranks students per class per semester according to marks scored by each. =DCount("*", "qryResults","TotalMarks>"&[TotalMarks] AND [Semester] is common AND [Class] is common)+ really waiting for any immediate help. thank you all

[andy49]

Any chance of some data to play around with?

[felixkiprono402@gmail.com]

=Dcount("*","QryRank","CalcTotal>&[CalcTotal] WHERE [Termdate] is common AND [Class] is common)

[felixkiprono402@gmail.com]

Andy, Pliz help me out as soon as possible. Thanks.

[andy49]

Felix you need a grouped query using queryrank as your start point. Group on term date and class

[felixkiprono402@gmail.com]

I ve tried that in vain. The Dcount() ranks all students even from different class and separate semester as a unit. I just want how to put those two other conditions right (i.e. to be of same class and same semester)
How do i put the condition in the DCount()?
Thanks for your advancement