Help with updating an average into a table

[jex57]

Hi al,
Please can someone assist with the code to update an average into a table.

I have a table - tblMark
Fields are mark_id (PK)
test_id
mark
testAvg


What I need to do is when I click a button - the averages are calculated - based on test_id
So My data looks something like this

Mark_Id 1 test1 Mark 5 testAvg 6
Mark_Id 2 test1 Mark 6 testAvg 6
Mark_Id 3 test1 Mark 7 testAvg 6
Mark_Id 4 test2 Mark 3 testAvg 2.67
Mark_Id 5 test2 Mark 3 testAvg 2.67
Mark_Id 6 test2 Mark 2 testAvg 2.67


I would like the code to calculate testAvg and then populate my table with this information
I have tired a variety of queries but cant get it to update and calculate average at the same time.

Any assistance would be appreciated.
Thank you

 

[alansidman]

You should run an aggregate query as follows. Calculated Fields should only appear in Forms and Queries. It is a spreadsheet mindset to update tables with calculations. You can get the results dynamically using a query.

SELECT Table2.Testnr, Avg(Table2.Mark) AS AvgOfMark
FROM Table2
GROUP BY Table2.Testnr;

If you update tables, then you will be constantly making changes to the dataset every time a value in your table changes or additional data is added This is not the concepts of RDBMS.

 

[jex57]

Thank you for the advice.
Unfortunately neither a query or form will suffice and the averages need to be stored as they are used in a number of different benchmarking reports and forms.

 

[alansidman]

Queries can be used as a record set for reports or exported to Excel for further analysis. Basic fundamentals of RDBMS is to store raw data and all calculations are performed in Queries for immediate and updated analysis. Updating tables with calculations is a spreadsheet mentality and not a database concept.

If you insist on having an updated calculated table (which I don't advise), then create a query that generates your expected results, then use that query as a record set and run a MakeTable Query. This will create a new table based upon the calculated results of your query.