Calculations with values in previous record(s)?

[kurazy]

I have a database where I need to record student data, specifically marks and absenteeism. The marks are recorded each term as a running total. I have a final mark which needs to be equal to the 4th term mark.

I also need to record the number of days absent each term with a total at the end of the year.

My problem is that the term 4 mark is a in the previous record and the total days absent needs to be the total of the previous 4 records (term 1 - 4).

I using a create table query and an update query but that was a no go.

[kurazy]

My apologies if I wasn't clear in my post. I also simplified my sample database.

In tblMarks record 5, the mark has been set as the same as the previous mark.

Mark Term
42 1
32 2
45 3
65 4
65 5 (This is the final mark that needs to be equal to term 4 mark)


In tblAbsent record 5, the number of days absent is equal to the sum of the previous 4 days absent.

Absent Term
2 1
2 2
4 3
2 4
10 5 (Sum of terms 1 to 4)

The year is broken up into 4 terms.

I hope this clarifies things.

[twinnyfo]

kurazy,

I cannot open zip files at work.

However, based on your descriptions, it may be that your table structures are not completely normalized.

My initial thought is that there should be no need for a "5th" term, but when data is displayed showing totals, it is calculated at run-time.

In terms of the marks, it is unclear whether these marks are averages or overall totals, so please clarify how the 4th quarter marks are determined.

Concerning the absenteeism, an aggregate query should be able to add the total days absent for each student (for the year).