Thanks so muchi made Rank2 function and call it in qryRank2.
Thanks @arnelgphere is a simple Top 7 query (qryTop7StudentMarks).
you need to modify it if you want to group by Year, Term, GradeDesc, Stream and ExaminationType
It works. However, it selects more than 7 Subjects in case Subjects tie. In case of a tie it should pick the 7 top tied Subjects.here is a simple Top 7 query (qryTop7StudentMarks).
you need to modify it if you want to group by Year, Term, GradeDesc, Stream and ExaminationType
SELECT qryAllMarks.*
FROM qryAllMarks
WHERE (((qryAllMarks.Description) In
(SELECT TOP 7 T.Description FROM qryAllMarks AS T WHERE T.StudentID = qryAllMarks.StudentID AND T.Years= qryAllMarks.Years And T.Term=qryAllMarks.Term And T.GradeDesc=qryAllMarks.GradeDesc And T.Stream=qryAllMarks.Stream ORDER BY T.Mark DESC)))
ORDER BY qryAllMarks.StudentID, qryAllMarks.Years, qryAllMarks.Term, qryAllMarks.GradeDesc, qryAllMarks.Stream, qryAllMarks.Mark DESC;
@arnelgp thanks.here is the Grouped Query:
Code:SELECT qryAllMarks.* FROM qryAllMarks WHERE (((qryAllMarks.Description) In (SELECT TOP 7 T.Description FROM qryAllMarks AS T WHERE T.StudentID = qryAllMarks.StudentID AND T.Years= qryAllMarks.Years And T.Term=qryAllMarks.Term And T.GradeDesc=qryAllMarks.GradeDesc And T.Stream=qryAllMarks.Stream ORDER BY T.Mark DESC))) ORDER BY qryAllMarks.StudentID, qryAllMarks.Years, qryAllMarks.Term, qryAllMarks.GradeDesc, qryAllMarks.Stream, qryAllMarks.Mark DESC;
If you see More than 7 rows for each students, check the Year, Term, GradeDesc and Stream if they are the same or not.
if they are not the same, that is another Group.
@arnelgp it is not working. I have tried the whole day. Thanks but if there is a function i have a feeling it will work. Thanks for your helphere is the Grouped Query:
Code:SELECT qryAllMarks.* FROM qryAllMarks WHERE (((qryAllMarks.Description) In (SELECT TOP 7 T.Description FROM qryAllMarks AS T WHERE T.StudentID = qryAllMarks.StudentID AND T.Years= qryAllMarks.Years And T.Term=qryAllMarks.Term And T.GradeDesc=qryAllMarks.GradeDesc And T.Stream=qryAllMarks.Stream ORDER BY T.Mark DESC))) ORDER BY qryAllMarks.StudentID, qryAllMarks.Years, qryAllMarks.Term, qryAllMarks.GradeDesc, qryAllMarks.Stream, qryAllMarks.Mark DESC;
If you see More than 7 rows for each students, check the Year, Term, GradeDesc and Stream if they are the same or not.
if they are not the same, that is another Group.
@arnelgp here it is. Look at frmStudents and assist me come up with frmcriteria to do the following:-then post your latest db.
Kindly have a look at the form frmStudentslike i said you Check the Year, Term, GradeDesc, Stream.
if they are The Same then they belong to Same Group.
I don't know what is the problem you have.
Thanks so muchcheck frmStudents reports.