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 22806 Registration date Sunday June 8, 2008 Status Contributor Last seen May 25, 2024 - 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 responses

yg_be Posts 22806 Registration date Sunday June 8, 2008 Status Contributor Last seen May 25, 2024 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
yg_be Posts 22806 Registration date Sunday June 8, 2008 Status Contributor Last seen May 25, 2024 5
Nov 16, 2016 at 05:25 PM
I totally agree with you : it will only work within a database, such as within MS Access.
0
Blocked Profile
Nov 16, 2016 at 05:28 PM
My bad, Sir. I read it as a spreadsheet problem. Your query should do it! Please forgive me for the interruption!
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 22806 Registration date Sunday June 8, 2008 Status Contributor Last seen May 25, 2024 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
yg_be Posts 22806 Registration date Sunday June 8, 2008 Status Contributor Last seen May 25, 2024 5
Nov 15, 2016 at 04:27 PM
Please describe the structure of your tables.
0
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
Blocked Profile
Nov 16, 2016 at 05:55 PM
"Group By" whatever the rank is will do!
-1