Students Rank Problem

10 years ago By chegekim
I am not good in programming but i have a passion for it. I am creating a Exam Grading system in VB 6.0 with MS Access as back end. I have a problem here: I have 2 tables in MS Access (tblGrades and tmpGrades) with fields StdRegno, Term, ExamType, AvgScore, AvgGrade. I would like to extract the positions using AvgScore but i don't know how to handle a tie (Where students have same average score) such that after say two students in number 2 tie, then the next number will be Position 4 (i.e 1, 2, 2, 4, 5, 6, 7). I hope this makes sense. So far i have done the following and i am finding it clumsy: 
  1. Private Sub Command2_Click()
  2.     Dim dbs As ADODB.Connection
  3.     Set dbs = New ADODB.Connection
  4.     dbs.Open "Provider=Microsoft.jet.OLEDB.4.0;Data source=C:\Sgs\Sgs.mdb"
  5.  
  6.    ' Select all records in the New Customers table
  7.    ' and add them to the Customers table.
  8.       dbs.Execute " INSERT INTO tmpGrades " _
  9.       & "SELECT StdRegno, Term, ExamType, AvgScore, AvgGrade " _
  10.       & "FROM tblGrades " _
  11.       & "ORDER BY AvgScore " _
  12.       & "DESC", , adExecuteNoRecords
  13.  
  14.      dbs.Close
  15. Set dbs = Nothing
  16.  
  17.     Call UpdatePositions ' Calling the UpdatePositions Procedure
  18. End Sub
  1. Sub UpdatePositions()
  2. Connection
  3.  
  4. Dim rs As New ADODB.Recordset
  5. Dim Pos As Integer
  6.  
  7. Set rs = New ADODB.Recordset
  8.  
  9. With rs
  10.     .Open "SELECT * FROM tmpGrades ORDER by AvgScore DESC", cn, 2, 3
  11.  
  12.     Pos = 0 'Initialize the position with position number 1
  13.     Do While Not .EOF
  14.          Pos = Pos + 1 ' increment the position
  15.          'Is there a way i can check if the AvgScore is the same and I don't increment Position ranking???
  16.         !Position = Pos ' update the position field
  17.         .MoveNext
  18.     Loop
  19. End With
  20.         MsgBox "Positions Updated...", , "updates"
  21. Set rs = Nothing
  22. End Sub
Thank you good people!! I would appreciate a more cleaner leaner code with an example. Enough Respect to you all. Simon
Submitted byOelasoron Wed, 11/06/2013 - 14:36

You can use an average score tracker (avgScoreTracker) variable to track if there is a change in the value of the average score (avgScore) variable. Pos variable will only be incremented if there is a change in the avgScore variable. The following is the modified version of your code: 
  1. Sub UpdatePositions()
  2. Connection
  3.  
  4. Dim rs As New ADODB.Recordset
  5. Dim Pos As Integer
  6. Dim avgScoreTracker	As Single    ' New
  7. Dim avgScore		As Single    ' New
  8.  
  9. Set rs = New ADODB.Recordset
  10.  
  11. With rs
  12.     .Open "SELECT * FROM tmpGrades ORDER by AvgScore DESC", cn, 2, 3
  13.  
  14.     Pos = 0 'Initialize the position with position number 1
  15.     Do While Not .EOF
  16.  
  17. 	avgScore = !AvgScore
  18.  
  19.  
  20. 	if avgScoreTracker <> avgScore Then
  21. 		avgScoreTracker = avgScore
  22. 		Pos = Pos + 1 ' increment the position
  23. 	End if
  24.  
  25.          ' Position is now set to its desired value...        
  26.         !Position = Pos ' update the position field
  27.         .MoveNext
  28.     Loop
  29. End With
  30.         MsgBox "Positions Updated...", , "updates"
  31. Set rs = Nothing
  32. End Sub
Hope this helps...

Guy! You are smart! You are almost there! The ties are now given one position as they should, the only problem is to assign the next position after the tie. It gives the Next avgScore next position, regardless of how many ties, which is not correct(it should skip position by number of ties). Example: Four students have average of 85.5, 84.0, 84.0 and 82.5 respectively. Then position should be like: 85.5 gets Position 1, 84.0 gets Position 2, second 84.0 also gets position 2 but 82.5 gets position 4 respectively as there are four students in total. Maybe a counter for the number of ties can be used but i know not how. Attached is a screen shot of what i am getting. Thanks once again Oelasor. And thank you for nice Signature. I am trying to fish buddy! Cheers!

Add new comment

About text formats