Update fields in a record of a table from values in a summation query

Iron Contributor

The Account Balance table maintains a single record for each year. The record stores the beginning balance for the year and the other fields to be updated each time I run the update query during the year. I would like to use the result in a report.


I need to select debits and credits for the current year in a ledger table and then sum them. Then I need to update the corresponding fields in the single record of the Account Balance table with those totals. I have one query which selects the ledger records for the current year. Then, I have a query that sums those values. Both of these queries work OK. My problem is with the Update query.  I created it in design view. When I attempt to run it, I get the message: “Operation must use an updateable query.” Here is the SQL code for the update query produced from the design view.


UPDATE [Account Balance], [SCAT_Ledger_YTD_Sum]

SET [Account Balance].[Bal_Date] = Now(), [Account Balance].[Credit] = [SCAT_Ledger_YTD_Sum].[Sum of Credit], [Account Balance].[Debit] = [SCAT_Ledger_YTD_Sum].[Sum of Debit], [Account Balance].[YTD_Balance] = [SCAT_Ledger_YTD_Sum].[Ledger_Balance] + [Account Balance].[Begin_Balance]

WHERE (((Year([Account Balance].[Bal_Date]))=Year(Now())));


I don't want to update the SCAT_Ledger_YTD_Sum query. Only the Account Balance table. The SCAT_Ledger_YTD_Sum query is the source for the totals to update with. Yet the update SQL syntax seems to be saying to update both the table and the query. Of course, the query cannot be updated. How do I make this work?




0 Replies