Operation must use an updatable query

  • Thread starter Thread starter MBG
  • Start date Start date
M

MBG

I want to update a field (TotalCost) on the parent table of a parent-child
relationship (a literal one!) with a value ([sumCost]![SumOfCost]) from a
(previous) query that sums the values in a field on the child table
(CostPerKid)according to parent_ID. Basically, I'm using a query value in my
"date to" field. Is that possible? I'm getting the error message in the
subject line.

Here's what it looks like; what am I missing?

UPDATE (Parents INNER JOIN Kids ON Parents.Parent_ID = Kids.Parent_ID) INNER
JOIN sumCost ON Parents.Parent_ID = sumCost.Parent_ID SET Parents.TotalCost =
[sumCost]![SumOfCost], Parents.Parent_ID = [Parents]![Parent_ID]
WHERE (((Parents.[Household Income])>60000));

Thanks for help!
 
I want to update a field (TotalCost) on the parent table of a parent-child
relationship (a literal one!) with a value ([sumCost]![SumOfCost]) from a
(previous) query that sums the values in a field on the child table
(CostPerKid)according to parent_ID. Basically, I'm using a query value in my
"date to" field. Is that possible? I'm getting the error message in the
subject line.

Here's what it looks like; what am I missing?

UPDATE (Parents INNER JOIN Kids ON Parents.Parent_ID = Kids.Parent_ID) INNER
JOIN sumCost ON Parents.Parent_ID = sumCost.Parent_ID SET Parents.TotalCost =
[sumCost]![SumOfCost], Parents.Parent_ID = [Parents]![Parent_ID]
WHERE (((Parents.[Household Income])>60000));

Thanks for help!
Click to expand...

No Totals query, nor any query including a Totals query, is ever updateable -
even in a case like this where it logically should be.

I'd suggest replicating the functionality of sumCost in a DSum() function call
and updating TotalCost to the Dsum expression....

OR MUCH BETTER:

Don't store the total cost in your table AT ALL.

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, as a
calculated field in a Query just as you're now doing it.
 
Back
Top