1 Reply Latest reply on Feb 9, 2009 12:17 AM by davidhead

    Newbie has complicated issue, average of students top marks

    SeaQuest

      Title

      Newbie has complicated issue, average of students top marks

      Post

      I'm wondering if someone can help me with this. I have a database that looks something like this:

       

      Student #  Course   Mark

      12345        2100     85

      12345        1701      82

      12345        1910      74

      12345        1212      62 

      23456        1604      78

      23456        1712      70

      23456        1532      68

      23456        1801      62 

       

      I need to insert a new summary field that displays an average of each students highest 3 marks. In the examples above there are four marks for each student but I only want the average of the highest 3 marks. The database is already sorted in student number order and then mark order (descending). Based on this the above table would look like this:

       

       

      Student #  Course   Mark   TopAvg

      12345        2100     85       80.33

      12345        1701      82      80.33

      12345        1910      74      80.33

      12345        1212      62      80.33

      23456        1604      78      72.00

      23456        1712      70      72.00

      23456        1532      68      72.00

      23456        1801      62      72.00


       

        • 1. Re: Newbie has complicated issue, average of students top marks
          davidhead
            

          Lets say that your table is called RESULT. You need to create a self relationship to a new table occurrence called result_RESULT (based on the RESULT table too) that matches the Student number i.e. a relationship to other results for the same student. This relationship must be sorted by mark in descending order.

           

          Then in the RESULT table, create a calculation:

           

          (

          GetNthRecord ( result_RESULT::Mark ; 1 ) +

          GetNthRecord ( result_RESULT::Mark ; 2 ) +

          GetNthRecord ( result_RESULT::Mark ; 3 )

          ) / 3 

           

          That will give you the answer you are looking for.