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.
On Fri, 26 Sep 2008 13:49:01 -0700, Jon Hamilton wrote: >I am trying to use a update query to update a column in one table with the >average of values from another table. 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. -- John W. Vinson [MVP]
