Is this possible?

[jnr927]

I have a tblOrders and a tblShipments.

I also have a qryNewQtyDue, which takes QtyDue from tblOrders minus qtyShipped from tblShipments. So it gives me a balance due.

Is there any way to take this "balance due" and update it to QtyDue in tblOrders?

Example:

tblOrders
OrderID - 123
QtyDue - 100

tblShipments
ShipmentID - ABC
OrderID - 123
QtyShipped - 75

My query comes up with a balance of 25.

I would like the balance (25) to replace the original amount due in tblOrders (100).

Is it possible?
Thanks for the help in advance!

 

[mresann]

As Pat Hartman would say, it is NEVER a good idea to contain calculated fields within a table, but use queries to calculate your data. This is a fundemental violation of table normalization rules, especially when it deals with financial transactions such as orders. Therefore, the Quantity Due field should NEVER change for that particular orderID.

What you should do is create a query based on the Order ID that sums up the number of shipments in tblShipments, and subracts that number from the Quantity Due field in the Order table, and base your reports on that query.