SQL foreach using table rows

Question

I have two Access tables. One table (table1) has a unique field, MyID and another table (table2) has several rows with MyID and another column, Data. I'd like to write an SQL statement that is similar to a foreach where all the values for MyID are selected and averaged from table2's Data and then updated in the MyID row under another field, Avg.

**Table1**
MyID
ID1
ID2
ID3  

**Table2**
MyID   Data   Mon
ID2    10     Jan
ID2    20     Feb
ID1    10     Jan
ID3    30     Mar
ID1    30     Mar

Expecting results like:

**Table1**
MyID    Avg
ID1     20
ID2     15
ID3     30

Maybe there's a better way to do this in SQL, but don't currently know.

Answer 1

UPDATE  table1
        INNER JOIN
        (
            SELECT Data1, AVG(columnName) avgCol
            FROM table2
            GROUP BY Data1
        ) b ON table1.MyID = b.Data
SET     table1.avgColumnName = b.avgCol

• MS Access update with join