Stock Balance in each store at each items, finally need help
After all this time, finally I hd to write to ask.
Kindly help me please...
I hd created a table called DATABASE 1 QTY
which appear all my data such as RECEIVING, TRANSFER, POS, INVENTORY STOCKS, etc.
I hv only one DATABASE 1 QTY::Qty field, that record all quantity in every transactions process.
and wanted to control it to appear in other several tables, such as ITEMS and STORES, per ITEMS, per STORES.
For info, I hv 1 table Store called "MAIN STORE" and 1 other table Store called "ANAK MAS". that 2 table will contain information of my stores.
I also have ITEMS table called ITEMS LIST, which contain every details of my Items. In every items, I put 2 field (ITEMS LIST::Unit on MAIN STORE) and (ITEMS LIST::Unit on ANAK MAS), bcoz I would like to appear the Qty of the selected Item in each store.
Here are some calculation that Im willing to do but tottally doesnt work : (for example in Units on ANAK MAS field)
Case (DATABASE 1 | QTY::Store Name = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) ) - Case (DATABASE 1 | QTY::From Store = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) )
Means : 1. Sum all Qty when the "Store Name" field says "ANAK MAS"
2. Sum all Qty when the "From Store" filed says "ANAK MAS"
3. Then substract ( no 1. - no 2. )
Store name field will define stocks comes in
From Store field will define stocks comes out.
And my layout in DATABASE 1 QTY will looks like:
Date --- Transaction Type ---Item Name--- Qty --- From --- To
21/8/13--PURCHASED --- Necklace A --- 5 --- (BLANK) --- MAIN STORE
22/8/13--TRANSFER --- Necklace A --- 5 --- MAIN STORE --- ANAK MAS
23/8/13--POS --- Necklace A ---3--- ANAK MAS --- (BLANK)
As you see that I only have 1 Qty (Quantity) field that will calculate based on "From" and "To"
If its a transfer, then "From" will decrease stocks and "To" will increase Stocks in the choosen Store. And will copy-paste all the method to other Layout such as : POS, WASTAGE, BREAKAGE, etc.
Now the problem is to create the function in every field in other table to call the value from table DATABASE 1 QTY.
I wanted to have a calculation in my Necklace A record in ITEMS LIST table, that show how many stock left on what stores.
It should be appear :
Units on MAIN STORE : 0
Units on ANAK MAS : 2
Can you please help me with this?
Thank you so much before!