calculate difference on every entry

[villica]

I have two tables. one is stock
and the other one is shipping information

Example Table Stock

PARTNO QTYONHAND
A 50
B 25
C 5
D 10
E 20

EXAMPLE OF TABLE SHIP

CUSTOMER PTNO SHIPQTY DIFFERENCE
ABC A 45 5
BDF A 10 -5
CDF A 50 -55
AER B 1 24


I Don't know how to get the difference column

For example for part A there is 50 in stock. If I ship 45 to customer A, The difference is 5 which is the new stock
If I ship 10 to customer B, There is only 5 in stock, so the differnce is -5.



Can anyone give me any suggestions on how

 

[ColinEssex]

Use a query. New field -

Difference:[QtyOnHand] - [ShipQty]

Although shipping 10 when you've only got 5 seems a bit odd. It would be better to have the check done afterUpdate of the QuantityToBeShipped field so the you can have a message saying "Not enough for this order" or something.

Col

 

[villica]

Use a query. New field -

Difference:[QtyOnHand] - [ShipQty]

Although shipping 10 when you've only got 5 seems a bit odd. It would be better to have the check done afterUpdate of the QuantityToBeShipped field so the you can have a message saying "Not enough for this order" or something.


thanks for your reply. I already have a query that calculates the difference, but I need it on every entry

pn qtyonhand
A 20



CUSTOMER PN SHIP DIFF
AVC A 18 2 (QTYONHAND IS NOW ONLY) 2
DG A 5 -3
(2 ON HAND -5) - 3
*** THIS TELLS ME THAT I NEED TO BUILD ANOTHER 3 UNITS TO SHIP THIS LINE