Inventory Managment

meggy98

New member
Local time
Today, 00:31
Joined
Apr 6, 2018
Messages
3
Hello there!

I am trying to create a database for my job. My boss would like me create an inventory database that tracks the raw materials that go into the products so that every time we manufacture a product in our production form, the materials used in that specific product is taken out of our material inventory and the product manufactured is added to our product inventory. I have no clue how to attach the materials to the products. I have tried a few different things like creating a form that will allow me to select my one product and add many materials into that using a sub form (ex. One pencil = 1, graphite, 1 eraser, 7" of wood and a metal band to hold the eraser) but that always creates duplicates in the raw materials form so I'm not sure how to make this functional. Any insight would be much appreciated. And also, the best possible way to make an inventory transaction query?
 
Megan, there are perhaps a gazillion ways to approach this beast. First, let me point you to the Search function of this forum, which is 3rd from the right on the thin blue ribbon that runs just underneath the box at the top of the page that tells who you are.

Your intro told in the "Welcome" topic told us you are new to Access. Let me therefore give you a reading assignment before I answer your question more directly. You might wish to search for these topics:

1. Database normalization. You can search for "normalization" inside the forum because we are a dedicated-topic site. For the general web, make that "database normalization." Then limit yourself to college sites and the ubiquitous Wikipedia.ORG article.s.

2. Modeling. Access is a way to make a data model of your business. The ideal case would be that you can make "the map the same as the territory" with a good model, and then you can track anything you need.

3. Junction tables. This will figure prominently in the answer to your question. This is a way to track many-to-many relationships - and your question contains one.

4. Assemblies - because I recall at least a few posts where the topic was similar to yours, where a company made assemblies of components, wanted to track both components and assemblies, and the relationship was ugly.

5. Synthetic keys - or you could try "autonumbers" - because the odds are that SOMEWHERE along the line you will need to have a unique but arbitrary key for table linkages (see "normalization" for what that means.)

6. JOIN queries - which will be workhorses for your inventory because you will work on queries that join tables together to merge the information from each table in a specific way. Look up Relationships when you are working on "normalization" because this topic (JOIN + relationships) is a major part of normalization.

OK, once you've satisfied the reading suggestion, or have determined that you already knew that, the way I might approach this is:

1. Table of products (assemblies) - with a suitable primary key (PK), name of the product, other data about the product AS A WHOLE but NOTHING about its parts.

2. Table of components - with a suitable PK, name of component, units of components (i.e. per unit, per kilogram, per foot, ...)

3. Junction table of "recipes" - where you have a foreign key (FK) to the product and a foreign key to the component. Plus perhaps how much of the component is consumed in making the product. You will have ONE recipe entry PER product PER ingredient. So there will be many entries for a product, one for each component. You will have many entries, one per product you make. If there is overlap of components then you will sometimes have several records for the same component, one for each product that uses that thing.

4. Transaction table - where you track arrival, consumption, sale, shrinkage, and manual corrections for components (and, for that matter, products). This is basically how you know how much is on hand. You NEVER EVER IN A GAZILLIOIN YEARS want to store the currently available amount in the component table. Instead, you build a summation query grouped by the component's PK. Each transaction is either a gain or a loss of the item being tracked, so the amount for the summation is either positive or negative. From this table, you can determine exactly how much of some component should be on hand at the beginning of the day. Every time you make a product, you have to generate a loss of components according to the recipe.

You will have transaction type (which helps you decide whether the amount is a gain or loss), the amount, the date, and if you are tracking cost, then you can store the cost of the components (usually per unit). I have run across such transactions as "Consumed" (part of the manufacturing process), "Sold", "Bought", "Returned", "Adjustment" (due to errors in inventory or due to damaged material). There could be others, it's up to you and your business model.

This basic layout (plus many bells and whistles) would allow you to write queries that sum the cost of all components in a recipe. Since you can do grouping in queries by date, you could do time-based projections of consumption. I'm sure you will find MANY good discussions using the search feature.
 
Thank you everyone for the fast replies. I currently have a product table and a raw materials table (They have no data atm) but if I am correct, Doc suggested creating one more table that will store my products with the materials attached? I might've read that wrong. I guess my first problem before I move onto a query for inventory transactions would be how to I attach the materials to the products? Also if they are connected in a sub datasheet, when they are taken out of the inventory will it remove the products and the materials(Subdata) or just the product (The main datasheet) ?? I could be completely wrong but that is something I have pondered.
 
meggy,
If you are new to database and access, search the forum for inventory/stock control info. You'll see it is not a trivial application and can get quite complex quickly. The video I mentioned is free and in less than 30 minutes will give you an overview of what's involved.
I also recommend you work through a tutorial or 2 on designing your tables and relationships --and testing the model before getting too deeply into a physical database.
The tutorials (RogersAccessLibrary) are in the info link I gave earlier.

Good luck with your project.
 
Pat's viewpoint is not wrong. If you keep your products on the shelves as well as the raw materials, both should count as inventory. But it does depend on your actual business model. There, neither of us can tell you what is exactly right.

Let me also advise you on another issue - and here, jdraw hit the nail on the head. An inventory of components and assemblies can rapidly become a nightmare. Not impossible - but requires a lot of up-front design work. You are going to have to do some SERIOUS fact-finding about your current business model.

Here is some advice to keep in mind - the "Old Programmer's Rules" for designing and implementing a project.

#1 - If you can't do it on paper, you can't do it in Access. What that means in practical terms is that Access doesn't "know" anything. It's a tool. It's as dumb as a box of garden tools. YOU are the subject-matter expert about your business. The only thing Access "knows" is how to build forms, reports, queries, and tables rapidly - if you know what you wanted to build.

Therefore, until you are sure that you have a "paper" description - a project guide document, call it - that tells you what you do in every situation, you are not ready to do more than implement a scaffold or framework. You are NOT ready to build in details until you already KNOW the details on paper.

#2 - Access won't tell you anything you didn't tell it first, or at least tell it how to tell you. As part of #1, you will define the outputs you expect from your project. Once you know what you want to see, you can analyze your project to verify that it has the data needed to support those outputs.

This is, in effect, working backwards from output through computations back to inputs to determine / verify that Access has a data source for everything you wanted to see. Some outputs will be just passing inputs along to the form/report. Some will be computed. Be sure you have what you need for each thing to be seen.
 

Users who are viewing this thread

Back
Top Bottom