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.

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.

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.

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?

Creating an Inventory System with Bills of Materials

DeanBabic
Brass Contributor
Mar 06, 2025
This link might be better:
http://access.mvps.org/access/modules/mdl0027.htm

Access 97 is a no go.
Here:
https://github.com/mpkasp/django-bom

Took me 5 mins to spin up a Web app George! Still not convinced with Python?
- George_Hepworth
  Silver Contributor
  Mar 06, 2025
  If you created a full-blown BOM web app that does everything the OP needs and wants, and did it in 5 mins, you have my admiration.
  - DeanBabic
    Brass Contributor
    Mar 11, 2025
    Spinning a Web app from github repo is just that.
George_Hepworth
Silver Contributor
Jul 26, 2021
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.
- JBa
  Copper Contributor
  Feb 28, 2025
  This link doesnt seem to work anymore - is there another way to get hold of the sample BOM database?
  - George_Hepworth
    Silver Contributor
    Feb 28, 2025
    His link still works for me. Try this one: BoM.zip
- CDuest
  Copper Contributor
  Jul 27, 2021
  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.
  - George_Hepworth
    Silver Contributor
    Jul 27, 2021
    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.

Forum Discussion

Creating an Inventory System with Bills of Materials

Share

Resources