Hello! I'm a new user, please try to be as simple as you can. Please check out my example below and see if you can help me.
I'm designing a database that tracks all the different kinds of Kool-Aid that I make along with their ingredients.. Each Jug is made differently with various combinations of ingredients. i.e. sugar, lemons, kool-aid mix, salt, ice, etc. Not all jugs are created equal, but I want to reflect the ingredients on the Kool-Aid Jug's record and I want to count how many of each ingredient is in and add them up.
I separated the database into different layouts and tables. One table/layout represents each jug of kool-aid. And I have 4 more tables/layouts sorting my jugs by type( they all have different formulas for calculating their ingredients because big jug, little jug, short jug, tall jug). I have about 30 different jugs and they all have different properties.
My problem, lies in getting all of the different tyes of ingredients to be reflected on the Kool-Aid Jug's layout in a way that I can tally how many let's say lemons I used, and how many bags of sugar were used. This is hard because not every jug has sugar, and not every jug with sugar has lemons. I can't combine them together, because of the distinct formulas each ingredient has.
I'd also like to do this without having to create 20 visble and empty fields on the Jug's layout because only 5 fields were used.