2 Replies Latest reply on Dec 15, 2016 10:06 AM by coherentkris

    Finding highest score if exam is reading




      I am struggling to wrap my mind around this probably really simple calculation. Any help would be appreciated. The database I'm developing is for storing student test scores for reading, writing, and math exams. These scores are imported from Excel and stored in a table called TestScores. This table contains columns such as StudentID, TestScore, TestName (reading, writing, math), TestDate, etc. I also have a table called StudentInfo, with fields such as StudentID, Name, Phone, HighestReadingScore, HighestMathScore, HighestWritingScore, etc. These tables are related on StudentID.


      I'm trying to calculate the HighestReadingScore field by using the Max function to find the highest score in the table for that student where the test name is Reading. I cannot figure out for the life of me the formula to do this.


      Any thoughts? Thanks much!

        • 1. Re: Finding highest score if exam is reading

          In order to use Max(  ), you would need a relationship that only matches to the records from which you want the max value. In your example, you'd need a relationship that matches to only the "Reading" test scores.


          This could be done with a self join relationship such as:


          YourTable::constReading = YourTable 2::TestName


          Where Reading always has the name of the test as might be done with a calculation field, Reading, in this example, would use "Reading" as the calculation (quotes included).


          Then Max ( YourTable 2::TestScore ) will return the maximum test score.


          Note that instead of constReading, you could use a global text field where you select the name of the test in order to see the max score for that test.


          ExecuteSQL is another option and one that does not require a relationship to get to the max score.


          Note that you can also use a "Maximum of" summary field, sort your records by TestName and get a report that lists the maximum scores for each test if you sort your records by testname so that they are grouped by that value. With the right layout design, you can get a single row for each Test by using a layout with a subsummary layout part and no body layout part.

          • 2. Re: Finding highest score if exam is reading

            here is just one way.. using relationships