Error on Query: Operation must use updatable query? (1 Viewer)

spet

Registered User.
Local time
Today, 17:52
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]));
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
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!
 

spet

Registered User.
Local time
Today, 17:52
Joined
Oct 5, 2018
Messages
38
So, I'm trying to take the information from that Totals query....and update the CheckStub record. Thoughts on how to do that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
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.
 

Micron

AWF VIP
Local time
Today, 18:52
Joined
Oct 20, 2018
Messages
3,476
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 28, 2001
Messages
27,001
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

Top Bottom