[AccessD] updating a field in the parent based on sum of the child

John,

<<Does anyone know if it is possible to do this update in SQL or should I
just
go do it my tried and true way?>>

  Just like it says; use a DSUM() in the update row and drop the sub query.
I've bumped into this too and have had to resort to the DSUM() approach.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, August 28, 2006 2:03 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] updating a field in the parent based on sum of the child

I built a query that sums a field in the child table.  I then joined the
summation query to the parent table (using the PK/FK)  in another query.
The resulting query is not updateable.  I RTFM and came up with this:
 
When the query is calculating a sum, an average, a count, or other type of
total on the values in a field, you cannot update data in the query. Also,
you cannot update a query that references a field in the Update To row from
a crosstab, a query, a select query, or a subquery that contains totals or
aggregate functions. To work around this problem, use the Domain Aggregate
function in the Update To row of an update query. You can reference fields
from a crosstab query, a select query, or a subquery that contain totals or
aggregate functions.
 
Of particular interest is the second sentence "you cannot update a query
that references ...".  I am just trying to manually update the data in the
parent table using a direct edit by hand so this does not seem an exact fit
but ...  I have always had this issue, never really solved it.  I always
result to code to open a recordset of the parent table, and iterate the
summation query looking up the parent ID in the parent table, then updating
the data in the parent table with the sum value from the summation query of
the child table.
 
Does anyone know if it is possible to do this update in SQL or should I just
go do it my tried and true way?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com