Update query average from second table

  • Thread starter Thread starter Jon Hamilton
  • Start date Start date
J

Jon Hamilton

I am trying to use a update query to update a column in one table with the
average of values from another table. StandID and ORACS are common fields.
One ORACS row will correspond to multiple rows for StandID

The following query gives me a data sheet showing the values that I need,
but I can't seem to write an update query to give me the desired results.

SELECT round(Avg(Tree.TreeGrowth),1) AS AvgOfTreeGrowth, Tree.StandID,
Stand_Boundaries.ORACS
FROM Tree INNER JOIN Stand_Boundaries ON Tree.StandID=Stand_Boundaries.ORACS
WHERE (((Tree.TreeGrowth)>0))
GROUP BY Tree.StandID, Stand_Boundaries.ORACS;

The closest I have come updates the column with the average of all the
values for each row.

UPDATE Stand_Boundaries INNER JOIN Tree ON
Stand_Boundaries.ORACS=Tree.StandID
SET Stand_Boundaries.AnualGrwth =
DAvg("TreeGrowth","Tree","Tree.TreeGrowth>0");

If anyone can help me with this problem, I would greatly appreciate it.
 
I am trying to use a update query to update a column in one table with the
average of values from another table.
Click to expand...

In general... *don't*.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
Back
Top