2 Replies Latest reply on Apr 18, 2012 7:46 PM by JohnBrookes

    I'm sure its trivial, but...

    JohnBrookes

      Title

      I'm sure its trivial, but...

      Post

      OK, I have a table with fields:

      IDStudent_NumberSubjectGrade

      Each student will have grades in several subjects.  I want to look at performance in one subject against another for the whole class.  So for example I want to find all students who have done physics and economics, and I want my output to be:

      Student Number Subject1 Grade 1 Subject2 Grade2.

      1234Physics74Economics55

      2345Phyiscs72Economics88

      3456Physics98Economics 72

      So I've got a solution that sort of works.  I make a copy of the file, and then make a layout based on the original file with the fields Student_Number SubjectGrade from the original file and fields  Subject2Grade2 from the copy.  Then I do a find on Subject and Subject2, and sure enough I get all the students doing Subject and Subject2.  But the subject that is actually displayed in Subject2 is not the one I searched on in that field.  It is simply the first subject entry for that student.

      Ultimately, I just want the output to be an excel file with Grade1 and Grade2 columns.

      Any help would be greatly appreciated.

        • 1. Re: I'm sure its trivial, but...
          Sorbsbuster

          You can do it with a self-joins and two global fields.  (ignoring this year's results versus last years', etc)

          Create two global text fields:
          gSubject1
          gSubject2

          Create two self-join relationships from your table of results

          ResultsTable::StudentID = ResultsTable::StudentID
          and
          ResultsTable::gSubject1 = ResultsTable::SubjectName

           

          and

          ResultsTable::StudentID = ResultsTable::StudentID
          and
          ResultsTable::gSubject2 = ResultsTable::SubjectName

          On the top of a list view show these two global fields.  Set one to Economics and the other to Physics.  On a list view set the StudentID and names, and then from each of those relationships show the Exam Result.

          This assumes your listing is currently one record for each student, each exam.  I think we would have split up the Students into one table and the examp results into another.  You can still do this by adding the Student Table to your setup.  The downside to the way you have done it now is the difficulty of finding a list or uniqu student records.  (You could put the idea into a sub-summary part, but the right way is two tables.)

          • 2. Re: I'm sure its trivial, but...
            JohnBrookes

            Thanks Sorsbuster.  Now I know why global fields are useful!