I need to create class exam position

[yahshuazane]

I am working on access database and need to give each student position based on their score in class.

For example:
A class with 5 students with the following scores:
Student Score
Student A: 32
Student B: 78
Student C: 90
Student D: 90
Student E: 21

I wish to generate their position thus:
Student Position in Calss
Student A: 4
Student B: 2
Student C: 1
Student D: 1
Student E: 5

Take note of the tie between Student C and D please.

Thank you.

[Kosmos]

here's one solution

You could sort their final grades in descending order. We'll call this tblStudents which is divided into fields Students and StudentScore and Ranking

Create a form and a command button. Don't assign any of the wizard stuff but just click cancel.

Change the name properties of the command button to cmdRankStudents...then...go to events and on click and click on the right hand side of the blank white box and go into the code builder...

put in the following code:

Private Sub cmdRankStudents_Click()
On Error GoTo Err_cmdRankStudents_Click

DoCmd.SetWarnings False

'Opening ADODB connections and record sets
Dim con As ADODB.Connection

Dim rsStudents As ADODB.Recordset


Set con = CurrentProject.Connection

Set rsStudents = New ADODB.Recordset

Dim X As Integer
X = 1

Dim Y As Integer
Dim Z As Integer

rsStudents.MoveFirst
Y = rsStudents.Fields("StudentScore")
rsStudents.Fields("Ranking") = X
rsStudents.MoveNext
Do Until rsStudents.EOF

If rsStudent.Fields("StudentScore") = Y
rsStudents.AddNew
rsStudents.Fields("Ranking") = X
rsStudents.MoveNext
rsStudents.Update
Else
Y = rsStudents.Fields("StudentScore")
X = X + 1
rsStudents.AddNew
rsStudents.Fields("Ranking") = X
rsStudents.Update
rsStudents.MoveNext
EndIf

Loop

' if you want student ranking to skip if you have two rankings of 1 then go to 3, just use the Z variable and use it as a counter by adding under both possibilities of the if statement (Z = Z+1) and therefore X = X + Z under the second if statement would be 3 if there was a tie for 1

' now close everything up (connections and recordsets)

rsStudents.Close

Set con = Nothing

Set rsStudents = Nothing

DoCmd.SetWarnings True

Exit_cmdRankStudents_Click:
Exit Sub

Err_cmdRankStudents_Click:
MsgBox Err.Description
Resume Exit_cmdRankStudents_Click

End Sub


And I think that should work like a charm...let me know if you have any problems :)

Cheers
Kosmös

p.s. if you happen to teach at my school...Brandeis University...I better take your class and get that X=1 ranking :) lol

[Kosmos]

to clarify...you should sort the field, studentgrades, in descending order...
If you are not familiar with access and don't know how to get to the onclick thing, you can just create this as a module and run the module to make sure it works...if it doesn't work let me know but if it does just look up DoCmd.RunModule and I'm sure there will be some place that will show you what to do from there.

I was new to access not too long ago... so I vividly remember what you're going through lol..... this, in fact, is the first time I've directly answered someone's question :)

[NeoPa]

I am working on access database and need to give each student position based on their score in class.

For example:
A class with 5 students with the following scores:
Student Score
Student A: 32
Student B: 78
Student C: 90
Student D: 90
Student E: 21

I wish to generate their position thus:
Student Position in Class
Student A: 4
Student B: 2
Student C: 1
Student D: 1
Student E: 5

Take note of the tie between Student C and D please.

Thank you.

Assuming a table called [tblScore] with a [StudentID] field (numeric) and a [Score] field (numeric) then you would need something like this (NB the standard ordinal positioning system for exams is a count of the number of entries that are higher + 1) :

SELECT [StudentID],
       [Score],
       DCount('[Score]','[tblScore]','[Score]>' & [Score])+1 AS Position
FROM tblScore
ORDER BY DCount('[Score]','[tblScore]','[Score]>' & [Score])+1

[NeoPa]

If other data (as yet untold) is required in the output then these can be linked in straightforwardly without complicating the query unduly.

[Kosmos]

aww man and I did all that work for nothing lol..well except of course the learning experience :)

[NeoPa]

aww man and I did all that work for nothing lol..well except of course the learning experience :)

Sorry Kosmos ;)
The learning experience is, after all, what we are all about here at TSDN though.

[yahshuazane]

Thaks you all. I will try out your suggestions and get back with the results. This is my first time here and I am amazed how you have all gone to assist me with my problem. Thanks again.

[Kosmos]

Sorry Kosmos ;)
The learning experience is, after all, what we are all about here at TSDN though.

:) indeed you are right....but given that I did not know that ranking system built into Outlook it seems I may have had a proper solution, as well :)

[NeoPa]

I'm pleased we could help.
It was an interesting question :).

[NeoPa]

:) indeed you are right....but given that I did not know that ranking system built into Outlook it seems I may have had a proper solution, as well :)

I'm not sure I follow you but I would certainly agree that your work was not wasted :) Good to see you posting as well - shows a good attitude.

[Kosmos]

it has two integers...one that counts the amount of times the loop goes through but the other integer only changes if the grades from the previous are not equal and it changes by being set equal to the counting variable...therefore it covers the divide from 1 to 3 if there are two 1's for example (but you have to add the variable Z in for it to do this) otherwise it will just detect for 1 and 1 and then go to 2 which also may be a preferred system.

I never thought I'd get to the point where I'd be able to help but it turns out my previous work made me a valuable commodity...and it was this site that helped...and plus...I'm a musician and a politics and latin american studies major so of course I'm always willing to help out the community lol

[NeoPa]

Cool Kosmos.
You'll be a senior member soon by the looks of things.
The more you help the more you learn; the more you learn the more you can help.
Good luck in your other studies too.

:)