Hi, i'm building a label database (for the labelling software we purchased) for a cake company.
I'm trying to make it as normalised as possible however i've come across a quandry that is causing brainaches...
I have created a many to many linkiung products and allergens (e.g.:
Product 1 | Milk
Product 1 | Eggs
Product 1 | Nuts
Then I have created a crosstab query which then puts the allergens as the columns and have used an expresion to replace the "1" result in a column where there is an allergen into the name of the column.
Then another query takes this query and turns it into various statements via expresions (e.g. "Contains: Milk, Eggs, Nuts", "May contain: Sesame", "Suitable for..." etc).
Works great and exactly what I want.
HOWEVER and here is where i'm struggling.
We have some products that have multiple launguages on where they are shipped to parts of Europe....
Again trying to be as normalised as possible I would like to have a tranllation table like this:
Milk | French for milk
Egg | French for egg
Sesame | french for sesame
Which is what I have setup, however I then get stuck as to how to actually link that data and cross tab it without using 30 odd cross tabs to create the ingredient dec.
Relationship diagram is attached, if you need anything else to make suggetsions let me know.
PLEASE NOTE i'm not a very SQL minded person so please explain any coded solutions
Any suggetsions please would be helpful.
Thank you
I'm trying to make it as normalised as possible however i've come across a quandry that is causing brainaches...
I have created a many to many linkiung products and allergens (e.g.:
Product 1 | Milk
Product 1 | Eggs
Product 1 | Nuts
Then I have created a crosstab query which then puts the allergens as the columns and have used an expresion to replace the "1" result in a column where there is an allergen into the name of the column.
Then another query takes this query and turns it into various statements via expresions (e.g. "Contains: Milk, Eggs, Nuts", "May contain: Sesame", "Suitable for..." etc).
Works great and exactly what I want.
HOWEVER and here is where i'm struggling.
We have some products that have multiple launguages on where they are shipped to parts of Europe....
Again trying to be as normalised as possible I would like to have a tranllation table like this:
Milk | French for milk
Egg | French for egg
Sesame | french for sesame
Which is what I have setup, however I then get stuck as to how to actually link that data and cross tab it without using 30 odd cross tabs to create the ingredient dec.
Relationship diagram is attached, if you need anything else to make suggetsions let me know.
PLEASE NOTE i'm not a very SQL minded person so please explain any coded solutions
Any suggetsions please would be helpful.
Thank you
