Link to home
Start Free TrialLog in
Avatar of Felix Kiprono
Felix Kiprono

asked on

Ranking Students in a normalised Ms Access Database (Ms Access 2010)

Hello Experts. I have an Ms Access Database 2010 with various tables holding data of students. The Database is normalized (to avoid redundancy) such that i Have a table tblStudents(Adm, Name, Class), tblExams(Adm, Sememster, Subjects, TotalMarks). so i want to rank students (grouped ranking) in a query according to Semeter and Class details, such that, students of same class in a certain semester are ranked together and so on and the rank should take care of the ties (same marks) in a way that those with same marks are given same rank and the next rank is skipped . Using One table I can Manage that.

THIS IS USING ONE TABLE WITH ALL DETAILS IN IT (NOT NORMALIZED)
The Table below the solution using the table tblResults(Adm, StudentName, Semester, Class, Marks)

SELECT t1.Adm, t1.StudentName, t1.Semester, t1.Class, t1.Marks, Count(t2.Adm) AS Rank
FROM tblResults AS t1 LEFT JOIN tblResults AS t2 ON (t1.Semester = t2.Semester) AND (t1.Class = t2.Class) AND  (t1.Marks <= t2.Marks)
GROUP BY t1.Adm, t1.StudentName, t1.Semester, t1.Class, t1.Marks
ORDER BY t1.Semester, t1.Class, t1.Marks DESC;

Any help will be appreciated. I have attached the database for you to see. Thank you
Q_29022013_2-1-.accdb
relationship-1-.PNG
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Unless a Student can take one (and ONLY one) Class, I'd suggest your tables are not normalized. If a Student can take Many Classes, then you need a table in between Student and Class to store that relationship.

I'm also not sure that your tblExams is structured correctly. Generally speaking, you should have a table for Exams that holds details of that Exam (the Name, Date, etc). You would then have another table that would relate the Student to the Exam - again, a Many-2-Many relationship, assuming more than one Student can take a single Exam.

That Student_Exam table would be where the Grade is stored, I'd think, since it would be an attribute of the unique combination of Student and Exam.

If you structure your data in this way, then it's fairly simple to get the ranking - just query that Join table and sort it appropriately.
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is closer to what you want.
Q_29022013_2-1-Updated.accdb
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

hi Felix,

I see you have created a new question here:
https://www.experts-exchange.com/questions/29022599/Correcting-ranking-step-in-ms-Access-2010.html
using some of the ideas from this thread ... please respond and close this thread if you want to start a different conversation, thank you.

also, the way to get rid of duplicates is to be able to rank a tie ... and how would you do that?
Avatar of Felix Kiprono

ASKER

thank all for your contributions
you're welcome, Felix ~ it was more of a group effort though ... please give all helpful posts an assist when picking answers ~ thank you.