Error: "Operation must use an updateable query"

[Brian]

I have a table that serves as a daily statement for an
operating account with balance forward, incoming
transactions, outgoing transactions, and ending balance.
I use an append query to create a record respresenting the
current date and yesterday's ending balance as today's
balance foward, then I am trying to use an update query to
update the incoming and outgoing tranacations. However,
these incoming transactions are stored in a separate table
where there could be several transactions in one day. So
to list the total incoming transactions I created a select
query to sum the incoming transactions for that date which
returned only one record. Then I was attempting to use an
update query to put this number into the Incoming
Transactions folumn for the corresponding date. If I use
a make table query instead of a select query for the
summation, the update works fine. I saw in the online
help where it suggests "To update a field using the
aggregate of another field, calculate the aggregate in the
update query itself, not a different query." I cant' get
this to work when i am trying to total multiple records in
the update query.

here are my sql statements:
Select query:
SELECT [tblBoAOperatingAcct-Out/Inflows].Date, Sum
([tblBoAOperatingAcct-Out/Inflows].[Out/InflowAmount]) AS
Incoming
FROM [tblBoAOperatingAcct-Out/Inflows]
WHERE ((([tblBoAOperatingAcct-Out/Inflows].
[Out/InflowAmount])>0))
GROUP BY [tblBoAOperatingAcct-Out/Inflows].Date
HAVING ((([tblBoAOperatingAcct-Out/Inflows].Date)=[Forms]!
[frmCashMgmtSys]![txtSystemDate]));

Update query:
UPDATE tblBoAOperatingAccount, qselBoAIncomingTrans SET
tblBoAOperatingAccount.IncomingTransactions = [Incoming]
WHERE (((tblBoAOperatingAccount.Date)=[Forms]!
[frmCashMgmtSys]![txtSystemDate]));

 

[Michel Walsh]

Hi,


You can't update using a SUM. An alternative is to use DSum( ), the VBA
function, or, like mentioned by Steve Dassin, to update multiple time
(assuming you can start with a zero):


UPDATE myTableWithInitialZeros AS a INNER JOIN tableWithData AS b
ON a.theDate = b.theDate

SET a.IncomingTransact = a.IncomingTransact + b.[out/in flow]



with DSum:


UPDATE myTable AS a SET a.IncomingTransact = DSUM( "[in/out flow]",
"tableWIthData", "theDate=" & a.TheDate )


assuming that the regional setting for the default date format is US.


Hoping it may help,
Vanderghast, Access MVP