Ranking students

[mrsoft100]

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

[Stewart Ross]

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'.

[mrsoft100]

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

[FishVal]

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

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Kind regards,
Fish

[NeoPa]

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]

[I]Field      Type        IndexInfo[/I]
StudentID  AutoNumber  PK
SName      String(50)
Mark       Numeric

We may have data of :

StudentID  SName     Mark
      1    Armund      95
      2    Brian       63
      3    Charles     80
      4    Donald      45
      5    Evelyn      63
      6    Francesco   98
      7    Gerald      63
      8    Herbert     75
      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.

SELECT [StudentID],
       [SName],
       [Mark],
       DCount('*','[tblStudent]','[Mark]>' & [Mark])+1 AS Position

FROM tblStudent

ORDER BY DCount('*','[tblStudent]','[Mark]>' & [Mark]),
         [SName]

[NeoPa]

You can also do it with a subquery (See Subqueries in SQL), although sorting the results is a problem without making THIS a subquery.

SELECT [StudentID],
       [SName],
       [Mark],
       (SELECT Count(*)

        FROM [tblStudent]

        WHERE tblStudent.Mark>tS.Mark)+1 AS Position

FROM tblStudent AS tS

ORDER BY [SName]

[NeoPa]

I should show the results I suppose ;)

StudentID  SName      Mark  Position | StudentID  SName      Mark  Position
      6    Francesco  98        1    |       1    Armund     95        2
      1    Armund     95        2    |       2    Brian      63        6
      3    Charles    80        3    |       3    Charles    80        3
      9    Iain       80        3    |       4    Donald     45        9
      8    Herbert    75        5    |       5    Evelyn     63        6
      7    Gerald     63        6    |       6    Francesco  98        1
      2    Brian      63        6    |       7    Gerald     63        6
      5    Evelyn     63        6    |       8    Herbert    75        5
      4    Donald     45        9    |       9    Iain       80        3

The ones on the right are not sorted by position.

[mrsoft100]

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.