Forum Discussion
Creating an Inventory System with Bills of Materials
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?
- 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