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.

[orange]

You may get some ideas on database and table design from this free data model.
And the associated business facts here.

[kurazy]

Thanks for the reply. It doesn't really help me though. My problem isn't with the table design.

[rpeare]

You don't perform those summaries in a query. That's what reporting is for. Is there a specific reason you want to do this in a query rather than on a report?

[kurazy]

I need to be able to export all the data to excel as well as create reports.

[June7]

Cannot have subtotal rows in a normal SELECT query. If you want to display detail records as well as summary data, build report using Grouping & Sorting with aggregate calcs in group footer.

A report can be exported to spreadsheet but often the results are not nice. Why Excel, why not a PDF?

Or a lot of VBA to write records to a 'temp' table.

[rpeare]

You could likely do something with a union query but as we've said, trying to subtotal in a query is not really the best idea. Creating a report and exporting the report to excel would probably be your best (I've never done this)

I'm enclosing a copy of your database doing subtotals with union queries, but again it's a very cumbersome thing to handle.

Students.zip