Adding a record to one table - a field in this record then updates the same field in another table

[ABB125]

Hello.

The title for this thread is a little confusing, so I will try to explain it better using the following example (note this is not the topic of my database, but I feel this will be easier to understand):

I have two tables, Orders and Customers. The fields are OrderID, OrderCustomer and OrderValue, and CustomerID and CustomerTotalOrders. (I have missed out several fields that I don't believe are relevant to my problem.)
My question is, is it possible to add a record to the Orders table, and then when the record is saved Access automatically updates the CustomerTotalOrders field using the new data from the OrderValue field? For example, let's say I have two orders:

OrderID OrderCustomer OrderValue
1 - - - - 1 - - - - - - - - - 13.47
2 - - - - 1 - - - - - - - - - 67.32

Is there any way I can make Access add the two OrderValue(s) for customer 1, and display this in customer 1's CustomerTotalOrders field? Using the data in my table above, this should come to 80.79.
Then, if customer 1 orders something more, when this new record is added, the additional OrderValue is added onto the existing amount in customer 1's CustomerTotalOrder, to give a new amount in the field?

Eg: customer 1's next order has a value of 36.89, so their CustomerTotalOrders field is updated to 117.68?



Thank you very much for any assistance for some or all of my problem. If I need to explain anything further, please ask.

[Minty]

Don't try and store that value, as you would need to try and update it anywhere any change is made to any order.

Simply calculate it - On a form you could use

=DSum("OrderValue","Orders","[OrderCustomer] = [CustomerID]"

And the value will always be accurate.

You could also create a query CustomerOrderTotals

SELECT OrderCustomer, Sum(OrderValues) as TotalValue
FROM Orders
GROUP BY OrderCustomer

And link that to your forms record source.

[ABB125]

Thank you very much.