Solved Threads (2 Viewers)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
i made Rank2 function and call it in qryRank2.
 

Attachments

  • students.zip
    177 KB · Views: 112

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
@arnelgp kindly assist me with the function or query to print only 7 SUBJECTS OUT OF STUDENTS done. Please i am defeated.
Only 7 SUBJECTS to be graded
 

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
@arnelgp I tried using this but it has given me headache:-
Please kindly use the attached database and help me come up with a query or function to print only 7 best perfomed Subjects out of all subjects done
Kindly. I really don't know how to apply what you said here because I was also following this thread:-
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:53
Joined
Feb 28, 2001
Messages
27,001
I will not actually step into your database, but there is a predicate that you can use that says "SELECT TOP 7 field-list FROM record-source WHERE filter ORDER BY some-fields;" Look at what arnelgp gave you and see if you can work in the TOP 7 qualifier.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
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
 

Attachments

  • students.zip
    182.9 KB · Views: 91

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
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
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
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.
 

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
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 thanks.
 

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
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 help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
then post your latest db.
 

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
then post your latest db.
@arnelgp here it is. Look at frmStudents and assist me come up with frmcriteria to do the following:-
1. Print all subjects
2. Print 7 SUBJECTS.(7 best perfomed Subjects even if there are ties to print only 7 e.g:-
1. Subject 1 -80
2. Subject 2 -80
3. Subject 3-80
4. Subject 4-80
5. Subject 5-80
6. Subject 6-80
7. Subject 7- 80
8. Subject 8 -80
9. Subject 9-80
10. Subject 10-80
11. Subject 11-80
12. Subject 12-80

(Print only 7 which means the marks will be (7 times 80= 560 out of 700)

Thanks.
 

Attachments

  • students.zip
    234.3 KB · Views: 98
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
like 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.
 

Attachments

  • students (2).zip
    199.7 KB · Views: 97

mercystone

Member
Local time
Today, 07:53
Joined
Sep 20, 2021
Messages
108
like 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.
Kindly have a look at the form frmStudents
1. Print ALL SUBJECTS
2. Print 7 SUBJECTS.

Please I am requesting you. Please.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
check frmStudents reports.
 

Attachments

  • students (2).zip
    190.4 KB · Views: 97

Users who are viewing this thread

Top Bottom