How to auto-update a field using a form

[jbutcher]

I have two tables with a one-to-many relationship, we'll call them Table1(one) and Table2(many). Table1 contains a field that is to hold the average value of a field in Table2 (call it "Value") based on a common ID (the one-to-many relationship). So:

Table1
"ID" "Avg"
1 SomeAverage1
2 SomeAverage2
3 SomeAverage3

Table2
"ID" "Value"
1 SomeValue
1 SomeValue
1 SomeValue
2 SomeValue
2 SomeValue
3 SomeValue
3 SomeValue

I have a form based on Table2 where a user can enter new records and, therefore, new values.

How can I have the "Avg" field in Table1 automatically update with the new average as new records are added to the Table2 form? Preferably as part of the AfterUpdate event of a particular control on the Table2 form.

Note that I also have a form based on Table1 and have successfully created a button on this form that accomplishes this task by calling a function and using DAvg. I have not had any luck with the Table2 form though.

I am very new to VB and Access programming so I am making stuff up as I go. If you can dumb it down as much as possible, that would be great!

Thanks in advance!

 

[MarkK]

Hey there and welcome.
Typically in a database you store raw data only. It is when you retrieve data from storage that you aggregate, sort and calculate.
In your example for instance, you could write a very simple query based on Table2 that would exactly mimic the structure and data of your Table1.

SELECT ID, AVG(SomeValue) As [Avg] FROM Table2 GROUP BY ID

And see how the calculation is embedded in the retrieval mechanism?

Store data in it's simplest form. Don't store results.
Cheers,