Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Question Unanswered: Sum of Top Scores

    Hi there!

    I have a table called Single Score. It has six fields:

    School, Classification, Full Name, Grade, Score, Event

    School = name of school
    Classification = Size of school
    Full Name = Contestants name
    Grade = grade of contestant (freshman)
    Score = Score on test
    Event = event the test was taken in

    There may be more than one record with the same name but different event.

    Here is my conundrum. I need a query that treats each school like a team, taking the top three individual scores from each and adding them together.

    It should pull into the query with these four fields:

    School, Classification, Score, Event

    There is a lot of other stuff in the database, but Single Score table is my main focus.


    Any suggestions?

    Thanks in advance!
    Attached Files Attached Files
    Last edited by KashLeeC; 07-24-11 at 03:30. Reason: Add File

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Sum of Top Scores

    If individual score ranking is created then it is easy to do this.

    1. I made a copy of the Single Score Table as Single ScoreNew and added a new field with the name: Rank.
    2. Created a Query with the name Query2 with filter criteria with Score Is Not Null Cases sorted on School Name (ascending) and Score Descending Order.
    3. Written a VBA Routine in a new Standard Module (Module1), the VBA Program is given below:


      Code:
      Public Function RankList()
      Dim db As Database, rst As Recordset
      Dim srlRank As Byte
      Dim prevSchool As String, curntSchool As String
      
      Set db = CurrentDb
      Set rst = db.OpenRecordset("Query2", dbOpenDynaset)
      curntSchool = rst![School]
      prevSchool = curntSchool
      
      Do While Not rst.EOF
           srlRank = 0
           Do While curntSchool = prevSchool And Not rst.EOF
                srlRank = srlRank + 1
                rst.Edit
                rst![Rank] = srlRank
                rst.Update
                rst.MoveNext
                If Not rst.EOF Then
                   curntSchool = rst![School]
                End If
           Loop
           prevSchool = curntSchool
      Loop
      rst.Close
      
      Set rst = Nothing
      Set db = Nothing
      End Function
    4. Since the records in Query2 is sorted on School and Scores in descending order it is easy to assign a Rank number 1 to the number of individual scores under the same school and each school records are ranked in the same way.
    5. After running the above program created a Total Query (Query3) using Query2 as source and selected 1 - 3 ranked items only for school-wise Totals.

    The database with the above change is attached.
    Attached Files Attached Files
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    You're close! I like what you did with the ranking thing. That gives me an idea.

    The problem is, I need each score per event, not score per school. Also, ties would have the same rank, then jump to the next number, i.e. 1st, 2nd, 3rd, 3rd, 5th.

    But I like what you've done. It gives me a good idea. Like I said, it's just ranked the wrong way.

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    The Event field can be combined with the School to Cycle through and update the Rank List. In Query2 Event field also must be included in the sorting list: School Ascending, Event Ascending, Score Descending. The modified Code is given below:

    Code:
    Public Function RankList()
    Dim db As Database, rst As Recordset, curntScore As Integer
    Dim srlRank As Byte, curntEvent As String, prevEvent As String
    Dim prevSchool As String, curntSchool As String, prevScore
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Query2", dbOpenDynaset)
    curntSchool = rst![School]
    prevSchool = curntSchool
    curntEvent = rst![Event]
    prevEvent = curntEvent
    curntScore = rst![Score]
    prevScore = curntScore
    
    Do While Not rst.EOF
         srlRank = 1
         Do While (curntSchool = prevSchool) And (curntEvent = prevEvent) And Not rst.EOF
           If curntScore < prevScore Then
              srlRank = srlRank + 1
           End If
              rst.Edit
              rst![Rank] = srlRank
              rst.Update
              rst.MoveNext
              If Not rst.EOF Then
                 curntSchool = rst![School]
                 curntEvent = rst![Event]
                 curntScore = rst![Score]
              End If
         Loop
         prevSchool = curntSchool
         prevEvent = curntEvent
         prevScore = curntScore
    Loop
    rst.Close
    
    Set rst = Nothing
    Set db = Nothing
    End Function
    The same Score Value have the same rank number.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •