Get Stock On Hand

[Gita21]

How to find Stock on hand for each product.
Product
------
ProductID(PK)

ProductSold
---------
ProductID (PK)
date(PK)
QtySold

ProductBought
-----------
ProductID(pk)
date(PK)
QtyBought

 

[dekcool]

Give some sample output you need

 

[Gita21]

I found a simpliar query that works for me.
But it uses 3 queries. I was wondering if there was a
way to just use one.

qrySumBought:-
SELECT [BookID], [BookType], Sum([QtyBought]) AS SumOfQtyBought
FROM BookBought
GROUP BY [BookID], [BookType];

qrySumSold:-
SELECT [BookID], [BookType], Sum([QtySold]) AS SumOfQtySold
FROM BookSold
GROUP BY [BookID], [BookType];

qryStockOnHand:-
SELECT [Book].[BookID], [Book].[BookType],
nz([SumOfQtyBought])-nz([SumOfQtySold]) AS [Stock On Hand]
FROM (Book LEFT JOIN qrySumBought ON ([Book].[BookID]=[qrySumBought].[BookID]) AND ([Book].[BookType]=[qrySumBought].[BookType])) LEFT JOIN qrySumSold ON ([Book].[BookID]=[qrySumSold].[BookID]) AND ([Book].[BookType]=[qrySumSold].[BookType]);

 

[dekcool]

try this one

SELECT ~ ~ ~ FROM [SELECT ~ ~ ~ FROM TABLE1]. AS QRY1, [SELECT ~ ~ ~ FROM TABLE 2]. AS QRY2