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
Location Problem
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Location Problem
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Location Problem
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
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Location Problem
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)
=-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)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Location Problem
Thank you Thank you! worked perfectly Your the master!
I use another access site no one came up with the answer
So again Thanks
I use another access site no one came up with the answer
So again Thanks
-
- 3StarLounger
- Posts: 390
- Joined: 26 Jan 2010, 16:02
- Location: Las Vegas, NV
Re: Location Problem
I always use this site for my Access questions.
Hans and the others are, by far, the best!
Hans and the others are, by far, the best!
Carol W.