DESCRIPTION MY PROJECT

„X In 1 Academic Session, e.g. 2008/2009 Academic Session, there are 3 terms, which are 1st, 2nd, 3rd term
„X In each of the Term are 10 Classes which are Primary 1, 2 etc
„X Each of the Class has various number of Students
„X Each Student offer about 15 subjects like mathematics, English etc
„X 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
        Session Id (pk)

Table 2- Term
         Term Number (pk)
         Term Id(pk)
         Session Id (fk)

Table 3- Class
         Class Number (pk)
         Class Id(pk)
         Term Id (fk)

Table 4-Pupil
        Pupil Number(pk)
        Pupil Id (pk)
        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

hi olaoul,
u will have to resdisgn your academic records table to include 3 new fields.
1.session id(fK) 2. term id(fk).3.uniqueterm(text fld)this field will concatenate sessionid and pupilid in academic table e:g 2007/2008 1245 or 2007/2008 1245.i:e assuming the student id is 1245.

    at form level,do the following
the fields created in the table level,will be added in the form level.
2.on the uniqueterm field on the form;change it 2 a combo box whose record source will be as follows
select from academic
uniqueterm, sum(cummulativescore)

on the row count property,change it to 2.

3.on the after update event of the uniqueterm,enter the following code
  Cumulative Score=uniqueterm.column(1)

4.select from the uniqueterm field the value that matches the studentid and the session id e:g 2007/2008 1245 assuming the pupil id is 1245 and the session is 2007/2008.this will automaticatically place the value in the sum(cummulativescore)in the row source property of the uniqueterm inton the cummulative score field.


to concatenate the pupilid and sessionid check concatenating fields in the ms access help file.
bye