how to grade students base on their scores

[Rdbui]

Can some one help me! I am trying to create the simple database to calculate the scores and grades for students. I have been learning Ms Access about 3 months and still a beginner . Anyone can help me to write the code for this problem below and how to apply it to my table and query.

Here is the problem

I have the table call “students” and I have the fields “ StudID”, “StudName”, “Score”. I want to grade for students base on their scores by 1st, 2nd,3rd etc

Maxscore = 20
TotalStudents = 10 (or variable)

This is the output I want to report.
StudID Name Scores Grade
1 John 20 1
2 Kim 20 1
3 Scott 18 3
4 William 18 3
5 Joshua 18 3
6 Andy 10 6
7 Tony 9 7
8 Blaire 9 7
9 Paul 8 9


I want to grade all students with the highest scores (20) are grade #1. Like above John and Kim has scores equal 20, so their grades are #1. Next, Scott, William, Joshua get scores 18 so all they get grades #3 instead of grade #2 because I already give two grade #1 for John and Kim so I have to skip grade #2 and I use grade #3 instead. Next Andy gets grade #6 instead of grade #4, #5 because I have three people get grade #3 so I skip grade #4,#5. Just do the same the process for the rest of students.
I hope you understand the problem. English is my second language so it is very difficult for me to describe the problem clearly.
Any help,would be greatly appreciated.

Thanks in advance

 

[KDg]

if create a form and place a list box ( with 3 columns )named lstRank on it along with a command button and then drop this code into the On_Click event of the button it will generate you a ranking. It's not pretty, sorry, but I've done it quick as its now 5 o'clock, hometime


Private Sub Command0_Click()
Dim strsql As String ' this will hold the SQL to get the data
Dim rstOrder As Recordset ' this is the recordset that will be built off strSQL and will actually hold the data
Dim dbs As Database ' will reference to this database, you can use this to query others if you use the path instead of
' CurrentDB
Dim x As Integer ' this is pointless really and just saved typing another line of code, tho' now I've added the
' comment i haven't saved anything....
Dim z As Integer 'this is the actual rank to be assigned
Dim y As Integer ' this is their position in the list - may be different to their rank score
Dim MyScore As Integer ' you may need to change this to single if you need decimal places, it will track the score of
' the last agent to compare to the next one

Set dbs = CurrentDb ' the table we want is in this database

' first get all the students into Descending order ( highest first )
Let strsql = "SELECT ONE.StudentID, ONE.StudName, ONE.Score " _
& "FROM ONE " _
& "ORDER BY ONE.Score DESC"

'pull them into a recordset so that they can be manipulated
Set rstOrder = dbs.OpenRecordset(strsql, 4)

'find the score of the first place student
Let MyScore = rstOrder.Fields(2).Value ' recordsets start with field 0 so 2 is the score in this recordset
Let z = 1
Let y = 1

Let Me!lstRank.RowSource = Chr(34) & rstOrder.Fields(1).Value & Chr(34)

Let Me!lstRank.RowSource = Me!lstRank.RowSource & Chr(59) & Chr(34) & rstOrder.Fields(2).Value & Chr(34) & Chr(59) & Chr(34) & z & Chr(34) ' fill in the rank


rstOrder.MoveNext
' now run through the rest giving them a score, this is where y comes in, it will track how many have been given
' the same score so that we don't get 2 people with a rank of 1 and then give the next a rank of 2...
Do Until rstOrder.EOF ' this will loop until all the records in the original query have been run through

If rstOrder.Fields(2).Value < MyScore Then
Let y = y + 1
Let z = y ' if they scored lower then the previous student they should be assigned their positional score
Let MyScore = rstOrder.Fields(2).Value
Else
Let y = y + 1 ' if they scored the same ( it won't be higher because of the ordering ) then just track
' the position in the list and not the ranking, you could just as easily do this with
'rstOrder.AbsolutePosition
End If

For x = 1 To 2
Let Me!lstRank.RowSource = Me!lstRank.RowSource & Chr(59) & Chr(34) & rstOrder.Fields(x).Value & Chr(34)
Next x
Let Me!lstRank.RowSource = Me!lstRank.RowSource & Chr(59) & Chr(34) & z & Chr(34) ' fill in the rank
rstOrder.MoveNext ' done with this one, onto the next
Loop

end sub

[This message has been edited by KDg (edited 10-28-1999).]

 

[Rdbui]

Thank you very much for your time to look on my problem and wrote me the code. I appreciated all your works. I have tried to apply it into my database like the way you show me, but I got the error at this line
Set rstOrder = dbs.OpenRecordset(strsql, 4)
Run time error ‘3061’ too few parameters. Expected 1.
and I did not know how to fix it.

Okay let me show you what I did.
First I created the form name “student” and then on the form design “student” by using the list box command I create 3 columns like you said and named it LstRank

Second I created the command button and On_Click_Event of this button I copied all the code you wrote into it.

Can I ask you these questions? When I created the list box I used the control wizards with the List box command is it right?
In addition, it gives me three options which one I pick.
I want the list box to look up the values in a table or query
I will type in the values that I want
Find a record on my form base on the value I selected in my list box.

Let strsql = "SELECT ONE.StudentID, ONE.StudName, ONE.Score " _
& "FROM ONE " _
& "ORDER BY ONE.Score DESC"

The ONE is here you mean the name of the table right.

Thanks
Rbui

 

[KDg]

Hi Rdbui, sorry for the glitch - it was me reading it wrong! the error is proabably because one of the field names is wrong. If you pasted it exactly then you'll need to change StudentID as I typed it to StudID. For the text box it's probably easier to skip the wizard and just change Column Count in the Format tab to 3 and Row Source Type in the Data tab to Value List.
As you say the table name should be
changed to 'students' as well. If you have any more problems please feel free to mail me