After hours trying to figure out how to get a sum from one table’s rows into another table (a fairly simple statement in SQL Server), I finally found this from peter57r
peter57r
awestrope,
Try this instead: it avoids the aggregate query.
UPDATE tblOptions inner join tblOptionsPricing on tbloptions.optionid = tbloptionspricing.optionid SET BasePrice= nz(BasePrice) + ([LastCalculatedPrice]*[Qty])
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21826239.html
(this was the search I used in google: updating table based on query operation must use an updateable query aggregate)
This is a completely different idea from the way it works in SQL Server, it adds up as it goes along. So, just be sure to empty the appropriate cell before running if you need to start from scratch (which I do). This is much better than using asp to update one row at a time which I was about to give up and do. I can’t use VB. This all used to be done in a module in Access called by a stored proc. But, Access does not let you call that module if you’re coming from asp.
However, you can’t use the nz function from asp either, so just change it to an iif, like this:
SET wins = IIf(wins Is Null,0,wins)+1;
Thank you Peter! You are a life saver today! 
