Menu
  1. Accueil
  2. Tech Forum
  3. Office Software

Ms Access ranking students [Closed]

Report
Posts
4
Registration date
Monday November 14, 2016
Status
Member
Last seen
November 18, 2016
 -
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
 -
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?
See more

4 replies

Answer 1 / 4
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
 4
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;
Thank you
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

View all 7 comments
Report
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
 4 >
Posts
4
Registration date
Monday November 14, 2016
Status
Member
Last seen
November 18, 2016
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.
Report
Posts
4
Registration date
Monday November 14, 2016
Status
Member
Last seen
November 18, 2016
 >
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
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.
Report
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
 4 >
Posts
4
Registration date
Monday November 14, 2016
Status
Member
Last seen
November 18, 2016
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.
Report
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
 4
I totally agree with you : it will only work within a database, such as within MS Access.
Report
Blocked Profile
My bad, Sir. I read it as a spreadsheet problem. Your query should do it! Please forgive me for the interruption!
Answer 2 / 4
Posts
12628
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 15, 2020
 4
Please describe the structure of your tables.
Answer 3 / 4
Posts
4
Registration date
Monday November 14, 2016
Status
Member
Last seen
November 18, 2016
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.
Answer 4 / 4

"Group By" whatever the rank is will do!
Ask a question