4 Replies Latest reply on Mar 14, 2012 8:59 AM by philmodjunk

    Calculating the difference between the latest and initial scores

    RichardAgnew

      Title

      Calculating the difference between the latest and initial scores

      Post

      I'm trying to create a field that calculates the difference between two records of the same field as follows:

       

      PORTAL (in Table: Customers), linking to related Table: MYMOP (where every 5th treatment a record is created, recording a score for each symptom)

      Example:

      Tx No          Sym 1       Sym 2

         1                6              4

         6                5              4

        11               2               1

       

      I'd like a field that calculates the difference between 'the latest score for symptom 1' and 'the initial score for symptom 1' (for each  customer)

      I've created a field that finds the MAX ( Treatment No ) - i.e. the latest treatment, but not sure how to look up the value for the 'symptom 1 score' for that record, or how to then find the difference between that score and the initial score.

       

      Any help would be much appreciated.