Access stock taking system

Silver Contributor

Jan 19, 2021

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.

Ivan__Torr

Copper Contributor

Jan 20, 2021

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 .



ProdID	ProdName	StockQty	ProdSize 1	ProdPrice	ProdValue
1	cabbage	2	kg	€ 1.20	€ 2.40
2	carrots	3	2.5kg	€ 2.20	€ 6.60
3	potatos	5	5kg	€ 6.00	€ 30.00
4	peas	2.5	2.5kg	€ 1.00	€ 2.50
5	turnip	2.1	kg	€ 1.25	€ 2.63
6	cream	5.4	litre	€ 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.


			StockTakeID1	StocktakeID2

ProdID	ProdName	StockQty	ProdSize 1	ProdPrice	ProdValue	StockQty	ProdPrice	ProdValue
1	cabbage	2	kg	€ 1.20	€ 2.40	6	€ 1.20	€ 7.20
2	carrots	3	2.5kg	€ 2.20	€ 6.60		€ 2.30	€ -
3	potatos	5	5kg	€ 6.00	€ 30.00	5	€ 5.80	€ 29.00
4	peas	2.5	2.5kg	€ 1.00	€ 2.50	8	€ 1.00	€ 8.00
5	turnip	2.1	kg	€ 1.25	€ 2.63		€ 1.23	€ -
6	cream	5.4	litre	€ 3.20	€ 17.28	4	€ 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

George_Hepworth
Silver Contributor
Jan 20, 2021
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 received
b) 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.
- Ivan__Torr
  Copper Contributor
  Jan 21, 2021
  Spoiler
  
  George_Hepworth
  George
  Thanks For that but still stuck on one thing. I have attached screen shots of My relationships and a form in design and form view.
  I need to get the fields filled in on TBlStockProd [StcokQty] and [StockTakeID] as i have done many times in this database.
  The problem I feel i have is that i need "a union Query" between the three tables.
  I understand why, but as soon as I link the child and master fields I loose the left join to the ProdID.
  Is there a way around this
  
  regards
  
  Ivan

Forum Discussion

Share

Resources