Stock calculation related

[atisz]

Hi,


I have a kind of invoicing and other stuff database in use from a while, and now I would like to make possible to print reports with current stock levels.

This is what I have now:

tblSupplied - 4 the supplied products
tblOrders - 4 invoiced products

I have made 2 queries, 1 for each of this tables, queries necessary for calculations.

qrySupplied
Product_ID (grouped by)
Supplied_Qty (sumed)

qryOrders
Product_ID (grouped by)
Ordered_Qty (sumed)

I want to use a 3rd query, named qryStock, for the stock output, but I don't know how to make it to show me the stock for all products, because if I just simply create an expression Stock=[Supplied_Qty]-[Ordered_Qty] the query will show only those products which are supplied an ordered too, but will not show those which are supplied but not ordered.

Can you help me on this? I did searched the forum on this, but I haven't found anything like this to get helped with.

Thanks, Attila

 

[neileg]

Make your Supplied_Qty negative. Then use a union query to union the two queries together and them sum the union query.

To make this easier, I would have put the ordered and supplied transactions in the same table, with supplied being negative. Then a simple summation by product gives you the stock.

 

[atisz]

Make your Supplied_Qty negative. Then use a union query to union the two queries together and them sum the union query.

Thanks Neileg for your reply, it was really helping in solving this issue. I made a union query and I also made Ordered_Qty negative, after this in a separate query I calculated the sum and added some extra fields needed.

To make this easier, I would have put the ordered and supplied transactions in the same table, with supplied being negative. Then a simple summation by product gives you the stock.

I think I haven't spoked it out the way it should. I use 2 separate table because in tblSupplied I enter all products delivered to me by suppliers, and in tblOrders the products that customers buy from me.

Thanks again for your help.

 

[jal]

I tried this with a UNION. The second query should take care of the situation where a product is in stock (in the supplies table) but was never ordered. I may have used different table names.


Select SupplyTotals.ProductID, TotalSupplied - TotalOrdered as Stock FROM
(
Select ProductID, Sum(Quantity) as TotalSupplied From Supplies Group By ProductID
) as SupplyTotals
INNER JOIN
(
Select ProductID, Sum(Quantity) as TotalOrdered From Orders Group By ProductID
) AS OrderTotals
On SupplyTotals.ProductID = OrderTotals.ProductID


UNION



Select SupplyTotals.ProductID, TotalSupplied
From
(
Select ProductID, Sum(Quantity) as TotalSupplied From Supplies Group By ProductID
) as SupplyTotals
LEFT JOIN Orders as O on O.ProductID = SupplyTotals.ProductID
WHERE O.ProductID IS NULL