Jan 18 2021 06:07 PM
Jan 18 2021 06:07 PM
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
Jan 18 2021 07:39 PM
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.
Jan 19 2021 09:10 AM - edited Jan 19 2021 09:12 AM
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.
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
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.
Jan 20 2021 05:50 AM
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 .
|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.
|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
Jan 20 2021 07:05 AM
@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.
Jan 21 2021 10:20 AM
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
Jan 21 2021 10:33 AM
If I can get the tables in question with enough sample data to see what's going on, it will be a lot quicker than trying to visualize from screenshots. If possible that will expedite things.
I think a UNION won't be needed, but, frankly I'm not that great with abstractions....
Jan 21 2021 02:53 PM - edited Jan 21 2021 02:54 PM
@Ivan__Torr We need the tables that record where products are removed as well. Those tables, as noted before, will be the tables used to record how many plates were served, and how much of each product is in each plate. Stock Take is not a separate table. You need a table of ProductInventory, which I think is called StockProd here. I'll set something something up to show you that, but without a way to get to the Product quantities actually used in day-to-day operations, we can't get a balance of remaining product.
Jan 21 2021 06:30 PM
Here's my suggested approach to counting inventory, or Stock, based on my understanding of how this process should work.
You record each product in the table, as I modified it. In any relational database application, the basic design principle is that each data point is stored one time in one place. I think, and this could be wrong, that you can actually buy products from one OR MORE suppliers. Brie Cheese, for example, could come from a local dairy or from a big producer. That means you can't record supplier in the Product table. It has to be stored as part of the transaction that receives Brie, say 50 pounds from the local supplier and 150 from the national producer. The same is true of other changes I made to what I consider the appropriate design.
I started out with a basic inventory as your initial stock take, some of the items are a bit arbitrary and intended to be examples. I added two additional, smaller intake events, or receipts of small quantities of products to illustrate how that needs to work, and one Stock Take event to account for shrinkage.
Actual products OUT will not be tracked in these tables, as I've stated previously. Your usage will be tracked in those tables where you consume products during the process of making up and selling plates. I.e. of the 50 pounds of Brie, for example, you might sell a cheese and cracker platter that requires 16 ounces, or one pound of Brie. When you calculate the total IN (150 +50) and subtract from that the 1 pound per platter sold (Say, for example 25 platters, or 25 * 1), you get a net of 175 pounds still in stock. We refrain from storing those calculated values anywhere; we store only the quantities received in total, and the quantities consumed in total, and calculate the net when we run a report.
Jan 23 2021 10:02 AM
@Ivan__Torr I must have missed this post in making my last reply.
I can accept your accdb for review, but based on the part I've seen, an EXTENSIVE refactoring is in order.
Since our conversation is limited to Stock Take, or more generally, inventory, I can only comment on that aspect. But, before you think about selling this as a commercial application, a good deal of work is needed. Sorry, that does sound harsh, but the fact is that properly normalized table design is crucial to a successful relational database application.