Access stock taking system

Copper Contributor

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

14 Replies

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

@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

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

@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.40
2carrots32.5kg €      2.20 €          6.60
3potatos55kg €      6.00 €       30.00
4peas2.52.5kg €      1.00 €          2.50
5turnip2.1kg €      1.25 €          2.63
6cream5.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 StockQtyProdPriceProdValue
1cabbage2kg €      1.20 €          2.406 €      1.20 €      7.20
2carrots32.5kg €      2.20 €          6.60  €      2.30 €          -  
3potatos55kg €      6.00 €       30.005 €      5.80 €   29.00
4peas2.52.5kg €      1.00 €          2.508 €      1.00 €      8.00
5turnip2.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.

northwindtemplateaccess.png

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.

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

 

 

@Ivan__Torr 

 

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

@George Hepworth 

George. I have attached a zip folder that i am using for testing. There is a lot of data in TblProdList that is needed for another part of the database.

 

@Ivan__Torr 

We'll take a look at it and maybe come back with additional questions. 

Thanks.

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

 

@George Hepworth Also, I see that some of the fields are those awful, counter-productive Lookup type fields. Combo boxes with Value lists. Unfortunately, Microsoft saw fit to foist them on unsuspecting new developers. 

@George Hepworth 

 

If you cab send me your email address, I will send it. i am making the system for resale so dont feel comfortable sending over an open forum

 

Regards

 

Ivan

@Ivan__Torr 

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.

 

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