Solved QTY ON HAND - STOCK

[gcreed]

AvailableQuantity: Sum([totalPurchaseData].[purchase_item_quantity])+(Nz((SELECT SUM(QuantityTransferred) FROM Transfers WHERE Transfers.ItemCode = [totalPurchaseData].[Item Code] AND Transfers.WareHouseCode = [Warehouse_tbl].[Warehouse_ID]AND Transfers.ProjectCode = [Projects_tbl].[Project_ID]),0))-(Nz((SELECT SUM([sales_Item_Quantity]) FROM [totalSalesData] WHERE [totalSalesData].[Item Code] = [totalPurchaseData].[Item Code] AND [totalSalesData].[Warehouse_ID] = [totalPurchaseData].[Warehouse_ID] AND [totalSalesData].[Project_ID] = [totalPurchaseData].[Project_ID]),0))

the problem is: when i transfer items to a warehouse that i didnt make an earlier purchase to, the quantity won't show the transfer + value.
Please help!

 

[arnelgp]

then add another Yes/No field to Transfer table signaling that it is a transfer from other whs and not from purchase.
then you add (the sum of quantity) whose field is set to True, to your query.

 

[gcreed]

i didn't quietly understand your solution, can you please explain or maybe send me an example please.

 

[arnelgp]

how many whs do you have?

 

[arnelgp]

when i transfer items to a warehouse that i didnt make an earlier purchase to, the quantity won't show the transfer + value

unless the info on Transfer table is incomplete (missing item id or whs or project), it will not be included.
this is what your criteria on your query.

 

[gcreed]

how many whs do you have?

multiple (not specific)

 

[gcreed]

everything works fine ONLY if i make a purchase with 0 quantity to all warehouses, because as you see the code above starts with purchase table

 

[gcreed]

let's say i have and ITEMCode "1" & 2 WH "Main Warehouse" "2nd Warehouse"
i create a purchase invoice for (ItemCode "1") and i select (wh1"Main Warehouse"). now when i transfer (ItemCode"1") to ("2nd Warehouse"), AvailableQuantity will deduct the quantity from "Main Warehouse" but WON'T add the new quantity to "2nd Warehouse" (unless i create a new purchase invoice with (ItemCode "1" to wh ("2nd warehouse" )quantity "0")

 

[gcreed]

i can share the db if you need it

 

[arnelgp]

please share.

 

[arnelgp]

which form is your Transfer?
can you use the Average Cost/Price.
you don't really know if FIFO or LIFO is being used for the transfer.
and the items don't bear the Price Tag.

 

[gcreed]

YES, im aware of the version i shared, i fixed it all. im just facing a problem with the availablequantity field in warehouse_items(query)

 

[arnelgp]

see Query1, see also Item_Form (avaliable quantity).

 

[gcreed]

Sir, I have checked your file. The problem is when i try to transfer From warehouse 1 To 4.
Quantity transferred will not show in (available quantity). (i assume because to purchase has been made on warehouse4)

 

[arnelgp]

did you see Query1?
if whs4 is Active it will show in Query1.

 

[gcreed]

Thank you very much, you have a brilliant mind. actually i thought you will not be able to solve it (my bad)
i still have to connect (Project_ID) because my stock has warehouses and projects. i make a purchases, sales and transfers to a selected project and a selected warehouse. can i send you the db to solve the issue, please?