Access stock taking system

Ivan__Torr 

Thanks. I see a number of things to comment on, many of which have little to do with the current Inventory question. Let's get those out of the way. 

• Multi-Value Field for PlatePic in TblPlating. As a matter of principle, most experienced Access developers refuse to use these things. I would recommend, therefore, that there be a separate table for the pictures and that the image files themselves be stored externally, not inside the accdb.
• Indeterminate relationships between tblSizes and TblRecipes and TblFoodProducts and tblProdList. These should be designated as one-to-many and they should have Referential Integrity Enforced.
• Several tables (TblOrders, TblSupp, and TblOrderPurch, as well as TblAllergens) which have no relationships identified. These need to be designated and have Referential Integrity Enforced on them. Otherwise it is all too easy to have "orphan" records added to tables.

Okay, a couple of other things could bear attention, but overall it otherwise seems fine.

It looks like TblOrderPurch is where incoming product quantities are stored. It's not clear to me, though, what the difference is between ProdQty and PurchAmount. ProdQty appears to be the value that determines how much you have received of each product, though. That means your Stock Take will be based on a query that Sums ProdQty by ProdID:

SELECT ProdID, Sum(ProdQty) As ProductQuantityPurchased

FROM tblOrderPurch

You can include additional tables to bring in things like ProdName from the TblProdList, or to bring in Dates from the TblOrders table to group purchases by time period (i.e. Group By OrderDate).

To get product quantity going out, it looks to be like "Plate" is the central concept, i.e. products are use in different recipes which are combined into "Plates". But I can't tell for sure how you want to calculate that and which "xxxQty" field(s) to use.

If I had to venture a guess, I would say that you'd need to include tblRecProducts to get the quantity per recipe, and join that to either TblRecipes or tblPlateRec, or both, to get to the RecQty and then join that to TblPlating to get to the ProdQtyPPlate in tblPlateProducts.  Once you get there you should have the quantity of each product used in the total number of plates sold. 

With that, you should be able to finally join the two queries on ProdID to get the quantity in and the quantity out for a final value of remaining quantity in stock.

I hope that helps your thinking.