By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Calculations with values in previous record(s)?

P: 2
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.
Attached Files
File Type: zip (29.7 KB, 30 views)
Jan 12 '15 #1
Share this Question
Share on Google+
2 Replies

P: 2
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.
Attached Files
File Type: zip Students(2).zip (28.1 KB, 38 views)
Jan 12 '15 #2

Expert Mod 2.5K+
P: 3,484

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).
Jan 21 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.