I am trying to create a database to be in part used as a calculator for production of mixtures. I have figured out how to generate the generic, this is how to make the mixture (i.e. recipe), but I want to be able to use the same recipe information to create the specific, lots of individual mixtures. In other words, the targets are in the recipe, but the actual amounts, along with the component information from the recipe, need to be new fill records.
My setup looks like this. I have a "Component" table that is referenced one to many to a "Mixture Component" table, so each "Component" can be in many "Mixture Components" with specific requirements (concentration, accuracy, units of measurement, etc.). The "Mixture Component" table is referenced by the "Part Number" table, so that each "Part number" can contain many "Mixture Components". The "Part Number" table contains the container size that is used in the "Mixture Component" table, along with the attributes of each "Component", to do the math to calculate the amount of each "Component". This is the general recipe for the “Part Number” that should be able to be re-used each time the Part Number is ordered. All this works great.
My problem is how do I set it up to where each time the Part Number is ordered, a new mixture record is made with the actual amounts of each "Component" recorded, keeping in mind the math using the "Part Number" container size, the "Mixture Component" unit of measurement, and the "Component" attributes has to be done in reverse in order to come up with the actual concentration.
If this is confusing, my apologies, but I am confused. Any help would be greatly appreciated.
Date Created 11/8/2019 8:04:07 AM
Date Updated 11/8/2019 8:19:13 AM
Joshua Cooper 11/8/2019 8:19:13 AM
I made a mistake in the above question. The "Part Number" and "Mixture Component" tables are in an unreferenced relationship with a match for the part number in each table. Sorry for the confusion.