running total (1 Viewer)

[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 do it. I am using access 97

 

[neileg]

Why do you want to store the difference in the table? You can calculate this in a form or query as you need it.

If you really must have it, run a update query that calculates the difference.

 

[villica]

thaks for your reply Neil. I just trying to find an easier way .

I know that storing calculated fields in a table is a bad idea
but I am not sure how else to do this.
TABLE 1
pn qtyonhand
A 50



TABLE 2

CUSTO PN SHIP DIFF
ADF A 45 5 (50ONHAND -45) = 5
DFD A 12 -7 (5ONHAND FOR SAME PART - 12) =-7


This tells me that I need to build another 7 units in order to ship

 

[neileg]

The trouble is that both the quantity on hand and the deficiency to meet orders are transient values. They will change as you build units, ship them and more or ordered.

In simple terms, I would have 3 tables, one for units built, one for units ordered and one for units shipped. Eg

TblBuilt
PartNo
Units
Date of production

TblOrdered
PartNo
Units
Customer
Date of order

TblShipped
PartNo
Units
Customer
Date of shipping

These tables would be updated whenever you build or ship or have units ordered.

Then build a query for the information you need. Eg the quantity on hand, or require to be built will come from a query with these fields:
QryStock
PartNo
Balance:TblBuilt.Units-TblOrdered.Units

Orders outstanding would be:
QryOrdOS
PartNo
Balance:TblOrdered.Units-TblShipped.Units

And so on.

Building a database is different to building a spreadsheet. The entries in a table usually don't contain the information you want, they contain details about what has happened or will happen. The required information comes usually from queries that manipulate the data.

Hope this helps.