Stock control

[JonoB]

Hi,

I have tblStockPurchaseDetail that records stock purchases (with fields such as PurchaseID, Description, StockCode, Price, Quantity, etc). I may obviously purchase the same StockCode more than once.

I also have tblSockSaleDetail that records sales (wih fields such as SaleID, StockCode, Price, Quantity, etc). Similalry, I will sell the same StockCode on multiple occassions during the year.

I would now like to create a stock control report. So, each stock code will be shown on a seperate row, showing total stock purchases and total stock sales, and the net stock for each stock code.

How do I go about doing this? I have thought about a union query, but not sure if this is the right approach as I have never used a union query before....

Thanks for the help.

 

[stburkeuk]

If you create a query, link both tables together and select the Stock details from the first table and the Sale details from the second.

Then using the 'Total' options on the query form (accessed by the Sigma icon on the toolbar) get the query to "sum" the items in the Sale details table and "Groupby" in the Stock details.