Forum Discussion
Access stock taking system
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.
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 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.
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.