I am working on a recipe database, in Base.
I have 3 tables
Recipes Recipe_ID Primary key
Recipe_Ingredients Recipe_ID abd RecipeSeqNo Compasite key and Ingredients_ID not a key
Ingredients Ingredients_ID
Realtionship extablished
On a from i have
Recipe table
Recipe_ingredients table linked by Recipe_ID
Ingredients table linked to Recipe_Ingredients by Ingredients_ID
It works. Almost
Each recipe has multiple ingredients. Displayed in the Ingredients table.
One ingredient selected from Recipe_Ingredients is displayed. I would all of the Recipe_Ingredients displayed in the Ingredients table
How do I do this?
Thak Yoy
You will need a subform for Recipe_Ingredients. Subform should show a tablecontrol. Ingredients_ID will be linked to a listbox. Then all Ingredients will be shown for one Recipe of the main form.
That is my setup. Ingredients is a subform of Recipe_ingredients. Linked by Ingredients_ID.
Only on ingredient is shown at a time. This is what has been selected in the Recipe_ingredients sunform.
All can be shown by clicking on each ingreient in Recipe_Ingredients.
Hello @mturbett
If you want to see another working recipe sample, look at this post > How to create report that can be used as a cookbook
.
Link to downloadable files are in the question.
Why? Mainform should be Recipes, subform should be Recipe_ingredients and in this subform is a listbox, which writes the Ingredients_ID and shows “Ingredients”. You don’t need a form for “Ingedients”.
OK. I am trying to run this piece of SQL in the Tools->SQL window.
SELECT “RECIPE”.“NAME”,
“INGREDIENTS”.“NAME”,
“INGREDIENTS”.“CALORIES”,
“MEASUREMENTS”.“Description”
FROM ( “INGREDIENTS”
LEFT OUTER JOIN “RECIPE” ON “RECIPE_INGREDIENTS”.“RECIPE_ID” = “RECIPE”.“RECIPE_ID”
LEFT OUTER JOIN “MEASUREMENTS” ON “RECIPE_INGREDIENTS”.“MEASUREAMOUNTID” = “MEASUREMENTS”.“UNIT_ID”
LEFT OUTER JOIN “INGREDIENTS” ON “INGREDIENTS”.“INGREDIENT_ID” = “RECIPE_INGREDIENTS”.“INGRNT_ID”
)
I get this error message
I am not finding anything to fix. Compared to Ratslinger recipe suggestion?
Help?
@mturbett
The image you present in not readable. Even if it were, have no idea of the tables you have or what the SQL is for. The link provided was to see the sample there. The ingredients on that post is a sub form using a table and not a query.
.
Not clear also why running in Tools>SQL and not a query window although should work in each. Also you have not stated what database you are using, your specific LO version and your OS. These may all be of help.
.
It may be best at this point if you provide a sample which contains the problem you have.
Put away the brackets. The error in the screenshot shows: Brackets are used at this point for a SELECT, not for defining the relations between tables.
Thank you for your comments. With them I have resolved my problem.
Comment about the square brackets, they are an artifact that shows up on errors when using the selection Tools->SQL. I use LO 7.3.1