Creating an Inventory System with Bills of Materials

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

[#2]

@CDuest Ken Sheridan has a demo relational database application called BoM (in BoM.zip) on his OneDrive. It demonstrates the proper way to create a Bill of Materials relational database application.

[#3]

Thank you!
Do you have any suggestions for logic behind removing an entire BOM from the main tblInventory? I believe an update query would work best, however I only have experience removing one part at a time based on a PartNumber and Quantity entered by a user.

[#4]

@CDuest Being inexperienced with what you are asking about, I can't offer definitive suggestions.

However, in general, the technique is to start with delete queries that remove the records on the "Many" side a relationship, which is going to be the case with a BoM system. Then, when child records are deleted, you can run a delete query on the "One" side table(s) in turn.

[#5]

My apologies, I may have been unclear with the term "remove" in my last response.
I do not want to delete the records from the database. I have a main table (tblInventory) that has all parts listed with PartNumber being the primary key. Each PartNumber record has a field for CurrentStock on hand, which gets updated based on a users entry of parts coming in or out of the system (either added or subtracted from current stock).
So far, I have been using forms (frmAddInventory and frmRemoveInventory) where the user enters a PartNumber and Quantity, and queries update the current stock accordingly.
What I'm searching for now is a solution for removing an entire list of parts (with various quantities) from the tblInventory at once. I have now created a tblBOMs table, following Ken's example from above that has a list of PartNumbers and Quantity required. The information is all in the database, but as an Access beginner I'm unsure how to set up the "Criteria" and "Update To" fields in the update query, since I want to update multiple records at once. Perhaps there is a better way to do this, but so far I am only familiar with doing it through update queries.

[#6]

@CDuest Ah, Thanks for the clarification. 

Actually, I would advise against that approach, which involves a calculated value for "CurrentStock".

In a traditional relational database application, one does NOT try to calculate and store values that are dependent on other values. The risks of going out of synch are too large.

When you want to see a current stock level, the recommended approach is to use a query that does the calculation based on "Total Received" and "Total Removed" instead of risking accuracy with frequent recalculations.

How does Ken Sheriden's relational database application handle that, btw?

[#7]

Ah okay, I will look into changing "CurrentStock" to a calculation rather than a stored field.
Ken Sheriden's system is very basic compared to what I'm working with. It only has 7 parts/assemblies, whereas my system has over 350 parts/assemblies (and still counting). His methods provided some insight and sparked some new ideas, however the system itself seems like it would get confusing with a higher volume of parts.
Do you have any suggestions regarding removing multiple parts at once? Regardless of whether CurrentStock is stored or not, I will still need to record the transactions removing BOMs

[#8]

@CDuest 

Again, I would expect that "removing" multiple parts at once would involve one or more delete queries, starting with the records to be removed from the child table(s). 

However, it's not quite clear to me what you mean by "...removing BoMs" in the first place, so it would require more details about what that means in the context of this application.