Load or InterRecord with Aggr?

[#1]

Hi,

I need help working out the best route for the following scenario.

I want the user to be able to pick two terms to compare a period. We then work out the progress for the selected terms. We have simple student data as below showing pupil scores per term for a subject. 

Pupil	Term	Subject	Score
Steve	T1	Maths	6
Steve	T2	Maths	7
Steve	T3	Maths	9
Steve	T4	Maths	14
Jim	T1	Maths	5
Jim	T2	Maths	8
Jim	T3	Maths	8
Jim	T4	Maths	13

Based on the progress we then want to summarise it as follows:-

Progress
Start	End	Acheivement
0	2	OK
3	4	Great
5		Super

So if a user picks Start Term 1, End Term 2 I would expect to see. This been the T2 less the T1 scores then the value rated against the lookup table.

Pupil	Subject	Score	Progress
Steve	Math	1	OK
Jim	Math	3	Great

I've attached the data above into the app attached.

Any help would be great.

Steve

[swuehl]

Maybe like this?

Pupil	Subject	Only({<Term = {T2}>}Score) - Only({<Term = {T1}>}Score)	Only( Aggr( If( Only({<Term = {T2}>}Score) - Only({<Term = {T1}>}Score) >= Start And Only({<Term = {T2}>}Score) - Only({<Term = {T1}>}Score) <= End, Acheivement) ,Pupil, Subject, Acheivement))
Jim	Maths	3	Great
Steve	Maths	1	OK