I need to update an existing record in one table with the result of an aggregation subquery SUM() from another table
This is my attempt which obviously doesn't work with the following error msg: QUERY MUST BE UPDATEABLE

UPDATE ChartofAccounts
   Set ChartofAccounts.[BalCur02] = (SELECT SUM(GeneralLedger.[Value]) FROM GeneralLedger WHERE GeneralLedger.[Period] = 2 AND GeneralLedger.[Account] = '2120')
WHERE C.[Account] = '2120';


Can u help me here?

Crazy-making, isn't it?

You can fool it by using DSum:

UPDATE ChartofAccounts
   Set ChartofAccounts.[BalCur02] = DSUM("[Value]", "GeneralLedger", "[Period] = 2 AND [Account] = '2120'")
WHERE ChartofAccounts.[Account] = '2120';


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

You are a life-saver - thanks a million mate!!!:D