Complex: Comparing First Record entry for each linked field against Second Record Ent

[AdamCLloyd]

Hi Guys, I am looking for some help generating some statistics from data held in an Access 2007 database.

The scenario is that we have tblLearner that contains personal information, name, contact details for our learners. We have a second table called tbl4WeekReview linked to tblLearner where every 4 weeks the learner is assessed and scored out of 10 in a number of categories.

What I want to do is produce statistics detailing the improvement from the first Review taken by all Students against the second Review for all students.

For Example (Made Basic):
ID from tblLearner is linked one-to-many to tbl4WeekReview to field keyLearner

tblCandidate may contain information like this:
ID NAME
1 Adam
2 David

tblReview may contain information like this:
ID keyCandidate dtReviewed intExamScore
1 1 01/01/2000 80
2 2 07/02/2000 70
3 1 01/05/2000 90
4 2 09/04/2000 85

What I need to be able to pull off are two statistics:
1) For Each Learner, what their improvement is from Result 1 to Result 2 (and further results in future, 3,4,5 etc.) So for Adam, I need to show that there has been a score increase of +10 between first and second review

2) For ALL Learners, what the improvement is from Result 1 to Result 2 (and further results in future, 3,4,5 etc.) So with this sample data, the average score for the first Results is 85 (80+90)/2. and the Average score for second results is 87.5 (85+90)/2 so the average increase in score is +2.5

I hope this makes sense, How would I go about this? Your help would be greatly appreciated.

 

[Uncle Gizmo]

I think you will find what you want here:
Rogers Access Library --- Excellent compendium of Access 2000 routines from Roger Carlson.

In this example it's called a "running sum" I think that's what you're after.