AnsweredAssumed Answered

Calculating the difference between the latest and initial scores

Question asked by RichardAgnew on Mar 14, 2012
Latest reply on Mar 14, 2012 by philmodjunk

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.

Outcomes