PRINT 7 SUBJECTS OUT OF ALL SUBJECTS DONE (1 Viewer)

[victorlindh]

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 let's call them STUDENT A, STUDENT B and STUDENT C.
STUDENT A

SUBJECTS- MARKS
1. ENGLISH- 80
2. WOODWORK- 90
3. CHEMISTRY- 70
4. BIOLOGY- 80
5. MATHEMATICS- 85
6. ELECTRICITY- 87
7. ART- 100
8. CRAFT- 77
9. MUSIC- 43

STUDENT B
1. ENGLISH- 87
2. WOODWORK- 90
3. CHEMISTRY- 79
4. BIOLOGY-80
5. MATHEMATICS- 79
6. ELECTRICITY- 80
7. ART-89
8. CRAFT- 10
9. MUSIC-89
10. PHYSICS 89

STUDENT C
1. ENGLISH- 70
2. WOODWORK- 87
3. CHEMISTRY- 76
4. BIOLOGY-88
5. MATHEMATICS- 78
6. ELECTRICITY- 23
7. ART- 89
8. CRAFT- 74
9. MUSIC- 77
10. PHYSICS- 100
11. GEOGRAPHY- 87

The query or function should only print the following best performed Subjects.
STUDENT A
1. ART- 100
2. WOODWORK- 90
3. ELECTRICITY-87
4. MATHEMATICS-85
5. ENGLISH- 80
6. BIOLOGY- 80
7. CRAFT- 77
(TOTAL MARKS- 599 OUT OF 700)

STUDENT B
1. WOODWORK-90
2. PHYSICS-89
3. ART-89
4. MUSIC- 89
5. ENGLISH- 87
6. BIOLOGY-80
7. ELECTRICITY-80
(TOTAL MARKS- 604 OUT OF 700)

STUDENT C
1. PHYSICS-100
2. ART-89
3. BIOLOGY- 88
4. WOODWORK-87
5. GEOGRAPHY-87
6. MATHEMATICS- 78
7. CHEMISTRY- 76
(TOTAL MARKS- 605 OUT OF 700)


Thanks so much.

 

[arnelgp]

add Autonumber to your Table:

SELECT T1.student, T1.subject, T1.grade
FROM yourTable AS T1
WHERE (((T1.ID) In (SELECT TOP 7 T2.ID FROM yourTable AS T2 WHERE T2.student = T1.student
ORDER BY T2.Grade DESC)))
ORDER BY T1.student, T1.grade DESC;

 

[victorlindh]

Thanks so much

 

[victorlindh]

Thanks.
What about in case of a tie in all subjects? E.g
1. Subject 1 -70
2. Subject 2-- 70
3. Subject 3- 70
4. Subject 4- 70
5. Subject 5- 70
6. Subject 6-70
7. Subject 7-70
8. Subject 8-70
9. Subject 9-70
10. Subject 10- 70


Will I use the same query?
Thanks so much.