I am trying to duplicate what we have in an excel file in a query in access. Our products are made of usually 2-3 components, essentially a kit. In Access, we already have the Inventory Qty for all components. In the excel file, we are computing the Product Qty using formulas within the cells that essentially take the lowest number of what component qty makes up the product (kit) and that becomes the available Product Qty (see above image)
I think we have all the queries we need to do this, I'm just not sure how to go about it. I've started with this concept with a new query:
1) I have the Inventory Summary which gives me the "Actual Available Inventory" field and those values are the accurate inventory of the component parts.
2) We have Product Component Junction table which has the ID for that table along with the assigned Component and Product IDs (ComponentID & ProdID).
You can see I started this but I'm lost on where to go from here. Any help would be appreciated.