Error on Query: Operation must use updatable query?

[spet]

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]));

 

[Deleted member 147267]

I suspect your underlying query is the cause. See if it falls into one of these traps.

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

 

[theDBguy]

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]

So, I'm trying to take the information from that Totals query....and update the CheckStub record. Thoughts on how to do that?

 

[spet]

I suspect your underlying query is the cause. See if it falls into one of these traps.

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

So, I'm trying to take the information from that Totals query....and update the CheckStub record. Thoughts on how to do that?

 

[theDBguy]

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.

 

[Deleted member 147267]

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]

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.