How do i bring forward coumn value from record from one table to another Table ?

[headwaters via AccessMonster.com]

DESCRIPTION for your solution

 In 1 Academic Session, e.g. 2008/2009 Academic Session, there are 3 terms,
which are 1st, 2nd, 3rd term
 In each of the Term are 10 Classes which are Primary 1, 2 etc
 Each of the Class has various number of Students
 Each Student offer about 15 subjects like mathematics, English etc
 Each Student write 3 Tests and 1 Examination Each Term, making it 9 Tests
and 3 Exams in 1 Academic Session.

See my Table structure

Table 1-Academic Session Table 2- Term Table 3- Class Table 4-Pupil
Session Id (pk) Term Number (pk Class Number
(pk) Pupil Number(pk)
Term Id (pk) Class Id (pk) Pupil Id (pk)
Session (fk)
Term( fk) Pupil Name

Class Id(fk)




Table 5-Academic Records Table 6- Subjects
Record Id (pk) Subject Id (pk)
Pupil Id (fk) Subject Name
Subject Id (fk)
Test 1
Test 2
Test 3
Total Test Score
Exam Score
End Of Term Score
Last Term Cumulative Brought Forward
Cumulative Score


Relationships
Table 1 1-m Table 2
Table 2 1-m Table 3
Table 3 1-m Table 4
Table 4 1-m Table 5
Table 6 1-m Table 5

Academic expressions on Calculated Controls
Inside Academic Records Form Columns with numeric data types
See the Academic Records Table Structure Above
Total Test Score=Test 1+ Test 2+Test 3
Term Score=Total Test Score + Exam Score
Cumulative Score=Term Score + Last Term Cumulative Brought Forward / 2

My Headaches

This is what I want to achieve
When “Term†= 1st
Let “Last Term Cumulative Brought Forward = 0
When “Term†= 2nd
Let “Last Term Cumulative Brought Forward = 1st Term. Cumulative Score
When “Term†= 3rd
Let “Last Term Cumulative Brougth Forward = 2nd Term. Cumulative score

Do I need to create separate Tables for each Term? If I need to, How do I
relate the Tables How do I program and execute it. Can my existing Tables
designs work? Please help me

Olaolu