Solved Threads (1 Viewer)

[arnelgp]

i made Rank2 function and call it in qryRank2.

 

[mercystone]

i made Rank2 function and call it in qryRank2.

Thanks so much

 

[mercystone]

@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]

Please @arnelgp

 

[mercystone]

@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:-

PRINT 7 SUBJECTS OUT OF ALL SUBJECTS DONE

Hello everyone. I am new in ms access. I am trying to make Students database for entering marks for Subjects done. I would really appreciate if someone helps me to make a query or function which will only select best 7 performed Subjects out of all subjects done. Example. The following students...

www.access-programmers.co.uk

 

[The_Doc_Man]

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]

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

 

[mercystone]

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

Thanks @arnelgp

 

[mercystone]

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]

here is the Grouped Query:

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]

here is the Grouped Query:

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]

here is the Grouped Query:

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]

then post your latest db.

 

[mercystone]

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.

 

[arnelgp]

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.

 

[mercystone]

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]

check frmStudents reports.

 

[mercystone]

check frmStudents reports.

Thanks so much