Update a table field by a calculated field (from a query)

[HelpMeAccess]

Hi,



How can I update a table field by a calculated field (from a query) using an update query? To simplify the problem;
Lets say I have 2 tables:
1- tblParts;
Part BalanceOnHand
A 10
B 20
C 30


2- tblTransaction;
ID Part QTY Trans
1 A 1 Receive
2 B 2 Transfer
3 C 3 Transfer
4 B 4 Receive
5 C 5 Transfer
6 C 6 Receive


for each part, the Qty Transferred should be deducted from received and the result should be updated in tblParts.
As a result at the end my tblParts should look like this:


Part BalanceOnHand
A 11
B 22
C 28




thanks,
Amir

[alansidman]

Calculated fields in Tables are not a good idea. Read this on a complete explanation of this rationale.

http://allenbrowne.com/casu-14.html

[HelpMeAccess]

Hi, thank you for the reply.
My intention is :
1) to build a query between tblparts and tbltransaction to calculate the new Balance on hand (11 22 28)
2) Use an update query to store these results in tblparts.
I don't want to store calculation inside my tables.

I appreciate your help in advance.

Thanks

[alansidman]

The issue is once you post a calculation to a field in a table it is set and does not update until the next update. In the interim, if you data is added receipts or disbursements, then the table is incorrect and if a user queries the table, they will be getting erroneous data. You will have to continuously update the tables. You would be better served to do the calculations in your query as you are planning and publish the data from the query. In that manner, it is always up to date and correct.

A basic premise of good data base design is that calculations should never be stored. They should be performed on the fly as needed.