Peter Betts
asked on
Select category, subcategory in MS Access table field
I am not sure how to structure this question, but I hope the images will explain. I am designing a recipe manager.
I don't know how to link the Recipes->Categories to the RecipeCategories table to be able to select
Fish and Seafood -> Salmon -> Atlantic -> Fillet so the Field Categories in the recipes table reflects 4 categories.
Perhaps I have designed it wrongly. Regards, Peter
I don't know how to link the Recipes->Categories to the RecipeCategories table to be able to select
Fish and Seafood -> Salmon -> Atlantic -> Fillet so the Field Categories in the recipes table reflects 4 categories.
Perhaps I have designed it wrongly. Regards, Peter
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is for personal use. All the samples I have some across only use one category. I wanted to be able select a category then a subcategory then a ..... etc. I never thought of using a multi keyword search ( because I never heard of such a thing being a novice) but that makes sense) I don't need to FIND the recipe by keyword, but be able to store more than one category in a field when creating one
ID - Recipe_Name -Categories - Ingredients- etc I wish I had the skills to convey what I mean better - sorry
I can add many ingredients, but need a mechanism to have the Food Category behave like the first image in this thread ie if I select Fish and Seafood I can then select its subcategory Salmon, Scallops, Prawns or whatever, if that makes sense. I looked at that BOM schema but it may as well have been in Mandarin.
ID - Recipe_Name -Categories - Ingredients- etc I wish I had the skills to convey what I mean better - sorry
I can add many ingredients, but need a mechanism to have the Food Category behave like the first image in this thread ie if I select Fish and Seafood I can then select its subcategory Salmon, Scallops, Prawns or whatever, if that makes sense. I looked at that BOM schema but it may as well have been in Mandarin.
ASKER
I can see I was on the wrong track for what I wanted. Your suggestions have put me on a new path and I can see how I can realise what I want to achieve.
Thanks
Peter
Thanks
Peter
Great. Create a category table so you have consistent names. Then create a recipe-category table to associate a recipe with multiple categories.
tblCategory
CategoryID (autonumber pk)
CategoryName
tblRecipeCategories
RecipeID (foreign key to tblRecipe, first field of unique index)
CategoryID (foreign key to tblCategory, second field of unique index)
This structure allows you to add as many categories as you want for each recipe. So, you can add:
Main Course
Potatoes, mashed
Carrots, diced
Corned Beef Hash
Pie Crust
to describe Shepard's Pie (my version at least)
tblCategory
CategoryID (autonumber pk)
CategoryName
tblRecipeCategories
RecipeID (foreign key to tblRecipe, first field of unique index)
CategoryID (foreign key to tblCategory, second field of unique index)
This structure allows you to add as many categories as you want for each recipe. So, you can add:
Main Course
Potatoes, mashed
Carrots, diced
Corned Beef Hash
Pie Crust
to describe Shepard's Pie (my version at least)
ASKER
Damn! Now I need to make a shepherds pie :) Thanks Pat, but you need a splash of Worcestershire Sauce ;(
If every category path is precisely 4 levels deep, you would simply store the recipesubcat3_id in tblRecipeCategories. If the path depth is variable and you are going to use four separate tables, then you need four cat/subcat fields and populate 1, 2, 3, or 4. OR, you would go with a self referencing table and store the lowest level.
Study a BOM (Bill of Material) or Chart of Accounts model. This will be similar.