Updating parent table from values in child table

[AccessKid]

I have a parent table with an amount column that stores the total amount of one or more child records' amount column. I created an aggregate query to total the amount on the child table per record in the parent. Then I created an update query to set the parent table's amount to the agregate query's amount but access returns an error saying that I should use an updatable query. Is there anything I'm missing or Access just cannot do updates using queries?

 

[Pat Hartman]

1. Access does support updateable queries. However, queries that include aggregate functions such as Sum() are not updateable.
2. It is poor practice to store this type of summary information since it violates second normal form. Best practice is to calculate the sum as you need it to avoid update anomolies.

 

[AccessKid]

Hi Pat:

Sorry if I didn't explain quite clearly my problem and you seemed to have missed it.

The update query has a table which is the one updated and an aggregate query linked to the table from which the new value for the table being updated comes. I was not updating the aggregate query. I understand about normalization and stuff but there are times when for logic and speed considerations, I compromise on this issue. So I'm back to my problem. Is there a way to specify in the query design which table is being updated so that Access will know that I was not trying to update the aggregate query but the table?

 

[Jon K]

The aggregate function in the first query rendered the subsequent update query inoperative.

You can change the first query into a make-table query to create a temporary table and base the update query on this temporary table.

 

[Pat Hartman]

I know it doesn't make sense that the query would become non-updateable simply because it joins to a query that contains an aggregate function, but that's the way it works. Sorry I wasn't more precise in my explaination. Depending on exactly what you are trying to do, you may be able to move the totals query so that it is a subquery. Having the aggregate query as a subquery will not prevent the main query from being updateable. I have seen a post here that shows a subquery being used for this purpose. Try searching the archives.

This also carries to other things such as security. If you join two tables but only have authority to update one of them, the query is non-updateable regardless of which table you are actually trying to update.