Update Query Fails

G

George R

Can anyone tell me why this update query does not work?

UPDATE tblLots INNER JOIN qrySumOfPmtsNotPosted ON tblLots.ACCTNBR =
qrySumOfPmtsNotPosted.AcctNbr SET tblLots.ThisYrPaid =
Nz([tblLots]![ThisYrPaid])+Nz([qrySumOfPmts]![SumOfCurrentYearDuesPd])
WHERE (((qrySumOfPmtsNotPosted.TotalsUpdated)=False));

I keep getting a message that the "Operation must use an updateable query."

Thank you for your consideration.
 
B

BruceS

George,
All queries used in an Access Update query must be "updateable". A
"summation" query is not updateable, so it fails.
Try creating a temp table with the summation data, then using it in the
update query.
Bruce
 
G

George R

Thank you Bruce. That worked fine. Interestingly, when I followed the update
action with one to delete the temporary table, the deletion occured before
the update finished. I had to rearrange the sequence of actions so the
deletion was preceeded by a couple of other actions.

BruceS said:
George,
All queries used in an Access Update query must be "updateable". A
"summation" query is not updateable, so it fails.
Try creating a temp table with the summation data, then using it in the
update query.
Bruce

George R said:
Can anyone tell me why this update query does not work?

UPDATE tblLots INNER JOIN qrySumOfPmtsNotPosted ON tblLots.ACCTNBR =
qrySumOfPmtsNotPosted.AcctNbr SET tblLots.ThisYrPaid =
Nz([tblLots]![ThisYrPaid])+Nz([qrySumOfPmts]![SumOfCurrentYearDuesPd])
WHERE (((qrySumOfPmtsNotPosted.TotalsUpdated)=False));

I keep getting a message that the "Operation must use an updateable query."

Thank you for your consideration.
Click to expand...
Click to expand...
 
Top