I have created a stock system with all relevant tables .My system can purchase products from relevant suppliers via email. I am trying to create a system from this that can do a stock count ( ie Physical on hand)I cant work out how I can get the system to remember the count as a history with the then purchase price. I have tried a make table but cant get any further than that Any help appreciated Ivan

Ivan__Torr While you've done a good job of introducing the CONCEPT, there isn't really enough detail in that description to offer any suggestions. "...all relevant tables..." could mean a number of different things to different people, for example. And none of them would be able to do much with that anyway because we wouldn't know if OUR assumptions matched the reality. Let's start with a relationship diagram. That'll get us started being able to ask you useful questions about the system and maybe lead to some suggestions. Thanks.

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 ProductQuantityPurchasedFROM 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.

George_Hepworth See attached. Sorry new to Access. Its amazing what Covid get a person up too.It is a Recipe and Dish coster . It also does allergens and Calorie counts on each dish. hope this helps

George_Hepworth I don't think I explained it right. What i am looking for is to do a physical stock count and compare the counts to each other. My thoughts would be the following. If i create a table as below . ProdIDProdNameStockQtyProdSize 1 ProdPrice ProdValue 1cabbage2kg € 1.20 € 2.402carrots32.5kg € 2.20 € 6.603potatos55kg € 6.00 € 30.004peas2.52.5kg € 1.00 € 2.505turnip2.1kg € 1.25 € 2.636cream5.4litre € 3.20 € 17.28 Where ProdID, Prod Price and ProdSize1 is from TblProdList and StockQty is a value that i can manually input from a form. the StockQty would be a calculated field. So I physically count my stock and input the value. I can then calculate a stock Value. What I need to be able to do is compare my opening stock on ProdID= 1 in one stock take to ProdID1 in the second stock take( month of week later) Because I have create da new table , the value will not be changed when updating prices from my purchases. StockTakeID1StocktakeID2 ProdIDProdNameStockQtyProdSize 1 ProdPrice ProdValue StockQtyProdPriceProdValue1cabbage2kg € 1.20 € 2.406 € 1.20 € 7.202carrots32.5kg € 2.20 € 6.60 € 2.30 € - 3potatos55kg € 6.00 € 30.005 € 5.80 € 29.004peas2.52.5kg € 1.00 € 2.508 € 1.00 € 8.005turnip2.1kg € 1.25 € 2.63 € 1.23 € - 6cream5.4litre € 3.20 € 17.284 € 3.20 € 12.80 Once I have the second stock take completed, I can calculate my usage so where ProdID =6 Usage = 5.4 - 4 + Purchases Thanks Ivan

Ivan__Torr I see.This is in addition to the recipe section and not shown in your current relationships?I suggest you look at some examples of traditional inventory methods, such as the one in the Northwind Template in Access.Basically, inventory control depends on tracking: a) items receivedb) items sent out or consumed And, for the purposes of Stock Take tasks, you also need to account for shrinkage. In my opinion, Shrinkage is just one form of items out, though. The way it should work, therefore, is that you have a table that tracks items received into your inventory by item, by quantity and by date. I don't see a value to storing product value, although I suppose you could include Product Price as of the date of receipt. We discourage storing calculated values in tables. Any calculation needed can be done in a query at the time it is needed.Each time you receive a shipment of produce, therefore, someone enters each of the items received into this table.The trick here is that you are consuming these items internally, not sending them back out as would be the case in a typical retail or wholesale operation. To get items out, you must refer to your other tables, as previously discussed, where you are tracking what items are used in what recipes and how many of those recipes are used in plates.Again, the sum of items in from the inventory table minus the sum of items used in plates is your current stock. Because the inventory has a "DateReceived" field and your plates tables should have a "Date" table as well, you can limit the inventory to specific time periods. And, after a stock take, you can add a NEGATIVE amount to the Inventory table to account for any shrinkage you find.

Access stock taking system | Microsoft Community Hub

George_Hepworth
Silver Contributor
Jan 19, 2021
Ivan__Torr
While you've done a good job of introducing the CONCEPT, there isn't really enough detail in that description to offer any suggestions. "...all relevant tables..." could mean a number of different things to different people, for example. And none of them would be able to do much with that anyway because we wouldn't know if OUR assumptions matched the reality.

Let's start with a relationship diagram. That'll get us started being able to ask you useful questions about the system and maybe lead to some suggestions.

Thanks.
- Ivan__Torr
  Copper Contributor
  Jan 19, 2021
  George_Hepworth
  See attached. Sorry new to Access. Its amazing what Covid get a person up too.
  It is a Recipe and Dish coster . It also does allergens and Calorie counts on each dish.
  
  hope this helps
  - George_Hepworth
    Silver Contributor
    Jan 19, 2021
    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.

Forum Discussion

Access stock taking system

Share

Resources