Update w/ nested select

[spinktec]

Is this a valid Access SQL statement:

UPDATE tbActivity SET [tbActivity].Hours =
(SELECT Sum([tbLabor].Hours) AS totalActivityHours FROM [tbLabor] WHERE ((([tbLabor].ParentActivity)='92')))
WHERE ((([tbActivity].Key) = 92));

Whether I run this statement from VBA by RunSQL or in the query builder I get the same error: "Operation must use and updateable query."

The SELECT portion returns exactly what I want which is the sum of all hours in the labor records. And the update works when I sustitute a value for the SELECT statement (below):

UPDATE tbActivity SET [tbActivity].Hours = 45
WHERE ((([tbActivity].Key) = 92));

 

[Galaxiom]

One a query includes an aggregate it is not updateable.

Write the subquery results to another table then update the main table from there.