Hi all, first time post and asking for some advice. Not sure if this belongs in the import section, or design section.
I'd like some feedback on the below in order to help achieve my goal of being able to assign values to all the ingredients in a recipe, and then query the recipe as a whole. This may be a method of importing that allows the query, or some preprocessing in excel that allows putting the data in to workable tables.
I have a large amount of data that looks like the below in each row of excel:
Recipe Table
Title Unit Category Ingredients Baked Bread Imperial Bread Flour
Water
Salt
Sugar
Yeast
What I'd like to be able to do is assign values to each of the ingredients so that later on I could give the entire recipe some values. Eg, if all ingredients are listed as allergy friendly, then I can assign an allergy friendly rating.
My thinking at the moment is to break out the ingredients in to a separate table:
Ingredients Table
INGREDIENT FRIENDLY Flour 0 Water 0 Salt 0 Sugar 0 Yeast 1
In this case:
Allergy Friendly is 0
Not allergy friendly is 1
Where I get hung up is how I could structure a query to say "Look at the ingredients in the recipe, refer to the table of ingredients and give me the sum of all ingredients that match an ingredient in the "Friendly" column, because I don't know how to approach looking at each ingredient separated by a line break in the Recipe Table.
Any suggestions as to a query that could handle this, or a more appropriate table structure would be warmly welcomed.