Location Problem

[keiath]

Hi Guys

Another problem I need help sorting out.

I have a stock control system that has tabs. On the 1st is a set of data that gets its information for else where, On another tab I have a stock activity sub form that gets its data from a table called stock activity. In the Sub form I have the following fields StockID, Dateofmovement, RefNo, Location, StockIn, StockOut. (This is a relationship via the stock control number in both the stock activity and stock control table, so that data from stock activity only shows data in the relating stock control form)

on the 1st tab I have Total Stock (this is a calulation of stockIn-stockout) that works fine. But I then need to show where that stock is. Warehouse1 or warehouse2

so that on the 1st tab i have the following information Total Stock=60 Warehouse1=20 warehouse2=40

How can I do this?

Please help its the last part of the jigsaw

Thanks

[HansV]

You'll need a table that contains the initial stock for each product in each warehouse.
You can then calculate the stock on hand by adding the StockIn and subtracting the StockOut amounts for each product in each warehouse.

See for example Inventory Control: Quantity on Hand on Allen Browne's website.

[keiath]

Well as you may have seen from the database I have the stock activity table that tracks all movements, this includes opening stock balances. so in the subform all the information is there, as you can see stock item deducted from warehouse1 same product deduction from warehouse 2 and same for inwards ect

So all I need is the calculation to do it:-
1st calculation is for total stock details is =Nz(Sum([StockIn]),0)-Nz(Sum([StockOut]),0) That you provide and thanks for that.
then I need another calculation the adds in the same way but by location so - if location=1 then =Nz(Sum([StockIn]),0)-Nz(Sum([StockOut]),0)

can this be done? as this way it would answer all my needs.

Hope that makes sense

Thanks

[HansV]

The sum for location 1 is

=-Sum(([Location]="1")*(Nz([StockIn],0)-Nz([StockOut],0)))

and similar for the other locations. (I had to use quotes because Location is a text field in the Stock Activity table)

[keiath]

Thank you Thank you! worked perfectly Your the master!

I use another access site no one came up with the answer

So again Thanks

[Carol W.]

I always use this site for my Access questions.

Hans and the others are, by far, the best!