Calculating the difference between the latest and initial scores
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)
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.