Update Query

[acamusc]

Hi, Good Morning : This is my problem:
I have to control expenses budget, I have a table "Master" with following fields:
-Year
-Project
-Account (Accounting code)
-Budget amount
-Expended

Another table "Voucher":
Year
Project
Account
Amount

With "Voucher" table I run a Query (Q1) with Totals, grouping Project, Account and SUM Amount = SUMofAmount. It works nice.

Then I tried to run a Updating query like this:

Master (Table) Q1 (Query)
Expended ---------------- SumofAmount

There are linked Fields Year, Project and Account.

I got following error message "The operation must use an updatable query", since I am updating a table and not a query I got confused.
Will thank any help.

 

[scriverb]

This should work for you. Why don't you post the SQL for both queries so that I may view it and see what the syntax looks like.

Bob Scriver

 

[scriverb]

Okay, I spotted something in your post. You are using an aggregate Sum function from the Q1(Query). ACCESS cannot use this value in an Update or Append query. See the following from the MSN page:
This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (total) was calculated for the field (using either the Totals row or a domain function in the Field row). To update a field using the aggregate of another field, calculate the aggregate in the update query itself, not a different query.

I think the easiest thing here would be to use the Q1 query as input to a make-table query and thus make a temporary table. Now substitute this temporary table for the query Q1 in your Update query. This should now work for you.


Bob Scriver

 

[acamusc]

Thanks a lot Bob, I tried in that way and I think it will work find.

Acamusc

 

[scriverb]

Great. I know this has been a nagging problem for many over the years.

Bob Scriver