CODEWITHSUNDEEP
ms-access
Bonifacey
Bonifacey

Reputation: 65

Access Help design multiple calculations with parameters

I'm stuck in deciding how best to do calculations. My main table contains marks for students, in which there are 11 subjects in each of the many exams....

I can easily visualize this in a query for each exam or even create a crosstab. I can get the average score in a report using calculated control box...

Question 1: How can I accomplish this in a query or vba instead of a report? I need a way in which I can compare performance in different exams, Do I also need to store the average in a table for this future reference?

Each exam (11 subjects) is identified by an examdate_id.... It can also be queried through parameters(ExamYear, Semester and examType) which I can get from a form's combo box

Question 2 follows later

[Main table: exams][1] [1]: https://i.sstatic.net/NJz3A.jpg

Upvotes: 1

Views: 37

Answers (1)

EliteRaceElephant
EliteRaceElephant

Reputation: 8162

You want a trigger that calculates the average result. Access does not support database-triggers so you have to use a workaround with an event in a form.

  • Add the attribute AverageScore to your Exams-table
  • Create another form based on your Exams-table. It should have the new attribute AverageScore
  • You can change the view into datagrid if you like so it looks just like a table.
  • In the form_load-event you retrieve all IDs from your students and update each AverageScore based on the Exams-table ID.
  • This solutions will update every single average score every time you load the form so it would be advisable to put it somewhere else. Favorably when you type in the indivual exam results.

Upvotes: 1

Related Questions