Creating an Inventory System with Bills of Materials (1 Viewer)

[CDuest]

Hi there,

I'm new to Access and I'm developing a database for a small business that includes an Inventory table, where all parts are listed. I am hoping to create bills of materials for assembled products (finished goods), which would include some parts from the Inventory table (parts included depend on the finished good). I want to have a front end form where the user simply selects which finished good has been ordered, with a "complete work order" function that will automatically remove the specified part quantities from the current stock in the main Inventory table. I am unsure on how I should approach this, through tables, update queries, etc.

Please let me know if anyone has developed a similar system, or any ideas on how I can move forward. All suggestions and expertise are welcome!

Thanks in advance.

 

[theDBguy]

Hi. Welcome to AWF!

Duplicate posts are highly discouraged to avoid any confusion during the discussion.

Are you just new to Access but well versed with database development? Otherwise, you may have picked a tough a business process to model for your first database project.

There may be inventory database samples to help you get started, but you may still need to get your hands dirty.

 

[GinaWhipp]

Welcome to the Forum!

Hmm, You're new to Access and you are developing a small database for inventory, bill of materials, work order, purchase orders with all the other pieces and parts necessary AND you want suggestions. How new are you? This is relatively a hard business model for your first database. I mean have you created the tables? Have you sat down with a pencil and paper and worked out your model?

Not trying to discourage you, just trying to determine where you are in process.

 

[CDuest]

Thank you both for your responses!
I have taken an access course on Udemy, and I've created tables for Inventory, Customers, Employees, Work Orders, Sales Orders, Suppliers Inventory added and Inventory removed (both added and removed record the transactions in a table, while update queries change the current stock in the main inventory table based on whats added/removed). I have a functioning database, the only thing I'm unsure about is how to handle the BOMs. In the current database, I could remove each item from the BOM individually and assign it to a work order, I'm just looking for suggestions on how to streamline this process (remove all parts at once). I have worked it out with pencil and paper, I'm just looking to someone with more experience to help me figure out the best way to handle this. I understand it is a difficult project to start out with, however I am willing to put in the time and effort to make it happen.
Let me know if this helps, and if you need any more info. Thank you.

 

[GinaWhipp]

Ah, glad to know you are not starting from scratch. Well, not sure this will work for you but this is what I do. In the Inventory table there are two additional fields: iBillOfLadingID and iStatusID (it's shipping status). When the inventory items gets added to the Bill of Lading but has not shipped yet the BOL ID gets attached to that item and the status gets updated to 1 which tells the database it's on a BOL. Once it gets shipped the status is then updated to 2 indicating it was shipped. Once it is shipped, gets a status of 2, it will no longer show in inventory. I do this so it does not take it out of inventory until it leaves the premises as it might not leave due the Order being cancelled in which case I can run an UPDATE query to remove the BOL ID and blank out the status field.

However, you could take the above and work that principle into your Transactions table instead of the Inventory table. Just remember you need a way to back it out in case the shipment never leaves the building. (In my case there is no partial inventory so no need for a separate Transaction table.)

Oh, and I don't use Work Orders I just add them to the BOL which can be printed out and used as a Work Order (and dioes) so that the inventory can be pulled.

Hope that helps.

 

[CDuest]

Okay, sounds like your BOL field is similar to how I'm using work orders. What I'm more concerned about is having a bill of materials for a finished product that contains different quantities of multiple parts from the inventory table. I'm wondering if there is a way I can have one table (or query) that acts as a bill of materials, so when that finished good is shipped I can remove the entire bill of materials from the inventory table.

Say there are 350 parts in the inventory table, but an assembled product uses 30 of those parts. I'm trying to find a way to remove them all at once instead of removing them individually. Does that help clarify?

 

[arnelgp]

there is a BOM lesson here: Microsoft Word - BOM-Access.doc (csusm.edu)

 

[GinaWhipp]

Okay, sounds like your BOL field is similar to how I'm using work orders. What I'm more concerned about is having a bill of materials for a finished product that contains different quantities of multiple parts from the inventory table. I'm wondering if there is a way I can have one table (or query) that acts as a bill of materials, so when that finished good is shipped I can remove the entire bill of materials from the inventory table.

Say there are 350 parts in the inventory table, but an assembled product uses 30 of those parts. I'm trying to find a way to remove them all at once instead of removing them individually. Does that help clarify?

Wouldn't the completed item go on the BOL? Oh, I see @arnelgp has posted a document. See if that helps as I would not be assembling the finished goods on the BOL that is where the completed item gets shipped.

 

[CDuest]

That BOM lesson is a little different from my situation.. I have a huge list of parts (over 300) and I have four different "assembled products" that use a variety of parts in different quantities (eg. 6 screws, 2 bearings, 1 shaft, etc..) I'm wondering if I can put these required parts in a table or select query, then somehow remove them all at once from the inventory table.
I have tried adding a field called ProjNameQTY in the inventory table where I add the quantity for the parts required (most rows in the column are zero, but the screws would have a 6, bearings would have 2, etc). This allows me to use a select query to generate a report for bill of materials for each project. What I'm asking is if there's a way to remove these parts simultaneously, perhaps by subtracting one column from the other? I need help with the actual method behind removing all parts at once.

 

[GinaWhipp]

Do you have a tables that hold the four items and the parts they need?

 

[CDuest]

As of now I just have the quantities in a field of the inventory table. I could make individual tables for each of the four items though. Would this make it easier to remove them all at once?

 

[GinaWhipp]

Not individual tables for each item, so...

tblMainItem - wagon
tblMainItemParts - linked to tblMainItem includes all the parts and there quantities linked from the inventory table.

Then on the BOM you would use the ID from tblMainItem and run a query to UPDATE the Inventory table.

 

[CDuest]

What would this update query look like? I'm struggling with the logic behind removing multiple parts at once, so far I have only made update queries that remove one part based on the part number and quantity entered by a user

 

[GinaWhipp]

You are updating (subtracting) the parts the tblMainItemParts table from tblInventory. Though in your case you might be adding to your Transaction table to be subtracted from tblInventory. Can't say for sure how you add\subtract parts from your tblInventory without seeing it. You need to do what works for the way you set it up.

 

[MattBaldry]

Hi CDuest,

I have built a working MRP system with Purchase Orders, Sales Orders, BOMs, Works Order, Stock and the lot. It is not fine tuned and still a work in progress, but I am happy to share some of my ideas. At my work place we use Sage Manufacturing which is purpose built for this, and I have taken their working structure and tailored it for myself.

It is still WIP, but I am sure I can help with table structure ideas and the like.

~Matt

 

[CDuest]

Hi CDuest,

I have built a working MRP system with Purchase Orders, Sales Orders, BOMs, Works Order, Stock and the lot. It is not fine tuned and still a work in progress, but I am happy to share some of my ideas. At my work place we use Sage Manufacturing which is purpose built for this, and I have taken their working structure and tailored it for myself.

It is still WIP, but I am sure I can help with table structure ideas and the like.

~Matt

Hi Matt,

Thank you for your response! Would you mind sharing a bit about how you're handling BOMs?