0

I'm trying to update the remaining amount of money on different debit cards. I have the 2 following tables : Cards and Transactions. I would like my result to be:

 Number|Initial_Amount|Remaining_Amount         T_ID|T_Date|Credit_Card|Amount
----------------------------------------       ---------------------------------
    123|          1000|     820                    1| 05/02|        123| 100
    456|           200|     150                    2| 06/02|        456| 50
                                                   3| 06/02|        123| 80

I tried to run the following Query :

UPDATE Credit_Cards
SET Credit_Cards.Remaining_Amount =( Credit_Cards.Initial_Amount-(
                                                                     SELECT  SUM(T.Amount)
                                                                     FROM Transactions AS T
                                                                     WHERE T.Credit_Card = Credit_Cards.Number));

But I get the following error : "Operation must use an updateable query"

What can be the problem ? I saw many answers about JOINs but not using one here so I don't understand...

Harvey
  • 213
  • 1
  • 3
  • 12
  • See this [post](https://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access) , this question was answered in the past. – asantz96 Aug 04 '20 at 14:58
  • 1
    Does this answer your question? ["Operation must use an updateable query" error in MS Access](https://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access) – asantz96 Aug 04 '20 at 14:59
  • Or this https://stackoverflow.com/questions/62822270/how-to-use-dao-recordset-to-update-a-table-using-a-non-updateable-query/62822596#62822596. Saving calculated, especially aggregate calc, is usually unnecessary and often bad design. If you can calculate the value for update, it can be calculated when needed. – June7 Aug 04 '20 at 17:31
  • Unfortunately none of the post you've sent was useful... What am I doing wrong ? Why can't I save a calculated field ? What do you mean by calculated when needed? Thank you ! @june7 – Harvey Aug 05 '20 at 09:03
  • @asantz96 I've seen this post before but didn't understand how it could solve my problem. What is the problem with my way of doing it ? – Harvey Aug 05 '20 at 09:07
  • An aggregate query is not an editable/updatable dataset. It cannot be used as data source for an UPDATE action, period. If you can calculate aggregate Sum() for updating a table field, you can just do that Sum() whenever you need that calculation. If you really MUST save this value to table, the links describe available methods: 1) use aggregate query as data source for an INSERT action to a 'temp' table and use that table as source for UPDATE; or 2) open recordset object of aggregate query and loop through its records; or 3) use domain aggregate function in an UPDATE action. – June7 Aug 05 '20 at 10:11

1 Answers1

0

I finally fixed it by using the subquery I wrote above as a saved Query, then I did another query in which I Joined my Subquery and my table on Credit_Card.Number, then used this Join to display the right info in my report.

Harvey
  • 213
  • 1
  • 3
  • 12