By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Ranking students

P: 6
To all Members,
I am keeping a database of students performance records using ms access 2003.In the table named student I have names, Admision number, marks of students per subject which are eight in numeber, total marks which is actually the addition of the indivual subject marks. I therefoe want to add a formular on one column named position so that the rank of a student depending on the total marks is automatically entered. Please help. Regards
Aug 21 '08 #1
Share this Question
Share on Google+
7 Replies


Expert Mod 2.5K+
P: 2,545
Hi. It is actually much easier to do this in Excel than it is in Access (or any other relational database). The reason is that relational databases have no concept of record position - so whilst it is possible to do a form of ranking using SQL it is not simple or natural to do so.

It is possible to sort records in any way you like, so you can always sort the records by mark in descending order to obtain a form of implied ranking, but actually assigning a numerical rank (1, 2, 3 and so on) is, as mentioned, not so simple. There is a Microsoft knowledge base article linked here on this topic. It requires solid skills in SQL which you may find challenging.

Personally, I woud use Excel for this purpose. In the College in which I work we use standardised Excel sheets to record all exam results for our students. We use anonymous publication of overall exam marks (publishing student number but not name) and do not explicitly rank our students.

-Stewart

ps I retitled your post to make it a bit more meaningful than its original 'please help'.
Aug 21 '08 #2

P: 6
Thanks so much Stewart Ross Inverness for your concern and welcome. I am actually new in programming and new too in the forums (I am a learner) and would wish to know more from all the members. Allow me refer you back to my question:

To all Members,
I am keeping a database of students performance records using ms access 2003.In the table named student I have names, Admision number, marks of students per subject which are eight in numeber, total marks which is actually the addition of the indivual subject marks. I therefoe want to add a formular on one column named position so that the rank of a student depending on the total marks is automatically entered. Please help. Regards

your suggestion has enabled me to stop struggling in the wrong direction, thanks again.

Now, the database is actually linked to a Vb 6.0 enterprise edition form. Is it therefore possible to write a code that would do ranking in vb and then store the same information to the position field in my database? Please help.

Thank you still for retitling my question. I woully welcome your mails through [removed email]. Be blessed
Aug 22 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Be blessed too. :)

I would be glad to help you, if you will help me to help you.
And as a first step you should describe buiseness logic of your project in a possibly detailed manner and post your current tables metadata.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Kind regards,
Fish
Aug 22 '08 #4

NeoPa
Expert Mod 15k+
P: 31,429
As we don't currently have your metadata (info about the layout / structure of the table) I will illustrate the sort of SQL you would need to display students in order of ranking.

First, we need to understand that ranking is usually done such that it is a measure of how many there are ranked before it. If all students scored the same marks then each would be considered to be in (level) first position.

Assuming the following very simple table metadata :
Table Name=[tblStudent]
Expand|Select|Wrap|Line Numbers
  1. Field      Type        IndexInfo
  2. StudentID  AutoNumber  PK
  3. SName      String(50)
  4. Mark       Numeric
We may have data of :
Expand|Select|Wrap|Line Numbers
  1. StudentID  SName     Mark
  2.       1    Armund      95
  3.       2    Brian       63
  4.       3    Charles     80
  5.       4    Donald      45
  6.       5    Evelyn      63
  7.       6    Francesco   98
  8.       7    Gerald      63
  9.       8    Herbert     75
  10.       9    Iain        80
We need to show all these fields in the results, as well as their position. The results should be displayed in position order too of course.
Expand|Select|Wrap|Line Numbers
  1. SELECT [StudentID],
  2.        [SName],
  3.        [Mark],
  4.        DCount('*','[tblStudent]','[Mark]>' & [Mark])+1 AS Position
  5.  
  6. FROM tblStudent
  7.  
  8. ORDER BY DCount('*','[tblStudent]','[Mark]>' & [Mark]),
  9.          [SName]
Aug 23 '08 #5

NeoPa
Expert Mod 15k+
P: 31,429
You can also do it with a subquery (See Subqueries in SQL), although sorting the results is a problem without making THIS a subquery.
Expand|Select|Wrap|Line Numbers
  1. SELECT [StudentID],
  2.        [SName],
  3.        [Mark],
  4.        (SELECT Count(*)
  5.  
  6.         FROM [tblStudent]
  7.  
  8.         WHERE tblStudent.Mark>tS.Mark)+1 AS Position
  9.  
  10. FROM tblStudent AS tS
  11.  
  12. ORDER BY [SName]
Aug 23 '08 #6

NeoPa
Expert Mod 15k+
P: 31,429
I should show the results I suppose ;)
Expand|Select|Wrap|Line Numbers
  1. StudentID  SName      Mark  Position | StudentID  SName      Mark  Position
  2.       6    Francesco  98        1    |       1    Armund     95        2
  3.       1    Armund     95        2    |       2    Brian      63        6
  4.       3    Charles    80        3    |       3    Charles    80        3
  5.       9    Iain       80        3    |       4    Donald     45        9
  6.       8    Herbert    75        5    |       5    Evelyn     63        6
  7.       7    Gerald     63        6    |       6    Francesco  98        1
  8.       2    Brian      63        6    |       7    Gerald     63        6
  9.       5    Evelyn     63        6    |       8    Herbert    75        5
  10.       4    Donald     45        9    |       9    Iain       80        3
The ones on the right are not sorted by position.
Aug 23 '08 #7

P: 6
Thanks Stewart Ross, I must really apologize for taking too long before responding to your tireless efforts to help me. As I said before I am actually new in programming and my initial steps into programing is by using VB 6.0. I have tried using the steps you indicated but it seems I do not know where exactly to place the code. I you could accept, I would be happy to send you my program so that you can evaluate it and give necessary advice I would further be glad if you could send me your email so we may chat over this when you are free mine is ** Edit - email removed **. I am stuck with ranking. Please help.
Jan 1 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.