Error on Query: Operation must use updatable query?

spet

Registered User.
Local time
Today, 03:20
Joined
Oct 5, 2018
Messages
38
I have the following query that I am trying to get a sum of deductions for a PayPeriod and update the CheckStub record with the totals. I am receiving the error: "Operation must use udatable query" when executing the following query. Any help would be much appreciated!


UPDATE CheckStubInfo, qrySumOfDeductions SET CheckStubInfo.Deductions = [qrySumOfDeductions].[SumOfAmount]
WHERE (((CheckStubInfo.PatronNo)=[qrySumOfDeductions].[Payee ID]) AND ((CheckStubInfo.DATE)=[qrySumOfDeductions].[Date]));
 
Hi. My guess qrySumOfDeductions is a Totals query. If it is, then it is causing your query to become read-only.

Edit: Oops, a bit too slow today... Sorry for the duplicate information. Cheers!
 
So, I'm trying to take the information from that Totals query....and update the CheckStub record. Thoughts on how to do that?
 
So, I'm trying to take the information from that Totals query....and update the CheckStub record. Thoughts on how to do that?
Hi. Based on "normalization" principles, you wouldn't really do that. In other words, storing "calculated values" is against basic database principles. But if you must, you could try using DLookup(), I guess.
 
Agree that it's usually ill advised to store calculations. If you remain stuck you could consider posting a db copy to cut to the chase.
 
Among other things, your syntax for the UPDATE is creating a Cartesian JOIN which is probably making Access very unhappy.

UPDATE CheckStubInfo, qrySumOfDeductions

That "comma" is actually causing a "matrix" to be generated for the thing to be updated and Access doesn't like having to guess which member of the matrix you meant. Particularly since the SET clause only names something in a single table so there is no method specified for combining records.
 

Users who are viewing this thread

Back
Top Bottom