Microsoft Access - Inventory System with BOMs

Question

Hello,

I'm new to Access and I'm developing an inventory database for a small business. I have made tables for Inventory, Customers, Employees, Sales Orders, Work Orders, 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). In the Inventory table, there is over 300 parts with part numbers and current stock. The business has several assembled products that use various quantities of different parts from the main table (6 screws, 2 bearings, 1 shaft, etc). I am hoping to create bills of materials for each assembled item, and be able to remove the specified quantity of the parts all at once.

As of now, I have added a field to the Inventory table called "ProjectNameQTY" where I add the quantity for the parts required (most rows in the column are zero, but the parts required for the assembled item have designated quantities). This allows me to use a select query to generate a report for bill of materials for each project. What I'm wondering is if there's a way to remove these parts simultaneously, perhaps by subtracting one column from the other in an update query? I need help with the logic behind removing all parts at once, as I have only used update queries to remove one part based on a part number and quantity specified by the user.

I'm open to any suggestions, please let me know if you need more information.

Answer 1

Just FYI... Crossposted at AWF.

creating-an-inventory-system-with-bills-of-materials.318791