Previous Topic Next Topic

Ms Access ranking students

Closed
TajUrRehman Posts 4 Registration date Monday November 14, 2016 Status Member Last seen November 18, 2016 - Nov 14, 2016 at 03:07 AM
yg_be Posts 20294 Registration date Sunday June 8, 2008 Status Contributor Last seen September 27, 2022 - Nov 18, 2016 at 07:43 AM
Dear All,
How can i rank my student in ms access to find thier rank using vb, macro, query, expression builder or code builder which one the easy way to do it and how?

4 replies

Answer 1 / 4
yg_be Posts 20294 Registration date Sunday June 8, 2008 Status Contributor Last seen September 27, 2022 5
Nov 16, 2016 at 09:51 AM
Sample query on table "ranking" containing two fields, "id" and "result" : 

SELECT r1.id, r1.result, 1+Count(r2.id) AS rank
FROM ranking AS r1 left join ranking AS r2
on (((r1.result)<[r2].[result]))
GROUP BY r1.id, r1.result
ORDER BY r1.result DESC;
2
Blocked Profile
Nov 16, 2016 at 04:32 PM
@yg_be

This will not work on an excel spreadsheet.
0
TajUrRehman Posts 4 Registration date Monday November 14, 2016 Status Member Last seen November 18, 2016 > Blocked Profile
Nov 17, 2016 at 12:25 AM
Dear Sir,
my id field do not have autonumber field i have textfield named admission no as student id. can i know in the mentioned coding for ranking has r1, r2 are these fields or its using only in programming language. (what are these r1, r2)?
0
yg_be Posts 20294 Registration date Sunday June 8, 2008 Status Contributor Last seen September 27, 2022 5 > TajUrRehman Posts 4 Registration date Monday November 14, 2016 Status Member Last seen November 18, 2016
Nov 17, 2016 at 12:14 PM
I provided the syntax of an MS Access query.
In the text I provided, you have to replace
- "ranking" by the name of your table
- "id" by the name of the column identifying the student, perhaps "[admission no]".
- "result" by the name of the column containing the marks.
By the way, I suggest avoiding spaces in names of tables and of columns.
"r1" and "r2" are just names used inside the SQL query, you can leave them as is.
0
TajUrRehman Posts 4 Registration date Monday November 14, 2016 Status Member Last seen November 18, 2016 > yg_be Posts 20294 Registration date Sunday June 8, 2008 Status Contributor Last seen September 27, 2022
Nov 18, 2016 at 12:51 AM
Dear Sir,
i am using the following codes in textbox for ranking students.
=DCount("*","4th","Anl1Marks>" & [anl1marks])+1
"4th" is table name "anl1marks" is field in this table.
Sir,
i just want in field "anl1marks" that which one has more marks it should be 1st position,
and less marks should be 2nd position e.t.c.
my formula is working but i want that equal marks should be count in one position and the next position should start in serial.
example: if have 10 students with equal marks 90 it should count 1st for all, and 5 students have 80 marks it should count 2nd for all 80 marks e.t.c
Thanks a lot for reply me.
0
yg_be Posts 20294 Registration date Sunday June 8, 2008 Status Contributor Last seen September 27, 2022 5 > TajUrRehman Posts 4 Registration date Monday November 14, 2016 Status Member Last seen November 18, 2016
Nov 18, 2016 at 07:43 AM
With your example and your formula, I assume you get "1" for the 10 students having 90, and "11" for the 5 students having 80. Is that what you obtain?
I understand you want a different result, and I do not know how to achieve what you want.
0
Answer 2 / 4
yg_be Posts 20294 Registration date Sunday June 8, 2008 Status Contributor Last seen September 27, 2022 5
Nov 15, 2016 at 04:27 PM
Please describe the structure of your tables.
0
Answer 3 / 4
TajUrRehman Posts 4 Registration date Monday November 14, 2016 Status Member Last seen November 18, 2016
Nov 16, 2016 at 02:01 AM
table is simple i just want to rank only one field name total obtaining marks of students i want to get rank as students position 1st, 2nd, and 3rd like.
the equal obtaining marks should be count one position in serial
thanks for reply me.
-1
Answer 4 / 4
Blocked Profile
Nov 16, 2016 at 05:55 PM
"Group By" whatever the rank is will do!
-1