calculate 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

 

[barbarossaii]

Part of the solution

Hi villica,

got a part of the solution:

create a query with both tables but don't define a relationship or join between the tables. Include in the query >Customer<, >PartNo<, >QtyOnHand< and >ShipQty<. This will create a table like this:

Customer PartNo QtyOnHand ShipQty
ABC A 50 45
BDF A 50 10
CDF A 50 50
AER B 25 1


In a report create the 'running' (?! sorry have a non English version) sum of the column ShipQty and subtract ShipQty from QtyOnHand (=>diff).

The report should show now:

Customer PartNo QtyOnHand runninSum(ShipQty) Diff=QtyOnHand-runninSum(ShipQty)
ABC A 50 45 5
BDF A 50 55 -5
CDF A 50 105 -55
AER B 25 1 24


I'm sorry, but I don't know how to create the 'running' sum in a query to 'complete' the solution....


HTH,

Barbarossa II