Forum Discussion
Creating an Inventory System with Bills of Materials
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.
This link doesnt seem to work anymore - is there another way to get hold of the sample BOM database?
His link still works for me. Try this one: BoM.zip
I think users are getting Access Denied. Including myself on both links
- 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.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.
- 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.
