how to catalog recipes

[J]

At my wifes request, she has asked me to build a db to catalog her
recipes. I attmepted to use the template which was on microsoft.com
however ran into a problem in the given situation.

I have a recipe with ingredients 1-10, so I have a table with recipes
(name, yield, where it came from etc) and a table with ingredients,
however on this particular recipe ingredient 10 is actually another
existing recipe in my recipe table. IS there a way through maybe a
union query (i ave no idea hence I'm asking) to be able to list
recipes as ingredeinst as well?

Does that make sense?

Thanks for your help!

J

 

[Al Campagna]

J,
tblRecipe should contain the name of the recipe, as well as any
ancillary info such as Servings, Instructions, etc...
It should have a unique key value... such as a RecipeID.
That's the One side of the relationship.
tblIngredients should list all the ingredients. It should have a
RecipeID (that relates to the correct Recipe) and an unique IngredientID
That's the Many side of the relationship.

If Recipe is related to Ingredients via RecipeID... then you can report
from the Recipe side of the relationship to Ingredients, OR from the
Ingredients to Recipe.
Example...
1 cup sugar
Apple Pie
Pecan Pie
Rhubarb Pie
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[J]

Thanks for the help, but I think I didnt explain myself very well. Let
me give you a literal example, say the recipe is for "spaghetti and
meat balls" and the ingredients are as follows:

Spaghetti (cooked)
Meat Balls
Spaghetti Sauce

The problem is that Spaghetti isnt from the can, its hoem made so
along with the recipe for "Spaghetti and meat balls" in tbRecipe there
is also a recipe for "Spaghetti Sauce."

Does this clear it up?

Thanks agai nfor your help!

J,
    tblRecipe should contain the name of the recipe, as well as any
ancillary info such as Servings, Instructions, etc...
    It should have a unique key value... such as a RecipeID.
    That's the One side of the relationship.
    tblIngredients should list all the ingredients.  It should havea
RecipeID (that relates to the correct Recipe) and an unique IngredientID
    That's the Many side of the relationship.

    If Recipe is related to Ingredients via RecipeID... then you can report
from the Recipe side of the relationship to Ingredients, OR from the
Ingredients to Recipe.
    Example...
    1 cup sugar
        Apple Pie
        Pecan Pie
        Rhubarb Pie
--
    hth
    Al Campagna
    Microsoft Access MVP
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."










- Show quoted text -

I

 

[Al Campagna]

J,
Hmm... that adds a bit to the problem.
I think I'd create 2 fields in tblIngredients.
Ingredient - Text and SubRecipe- Text

And, 1 field in tblIngredients
IsSubRecipe - T/F Boolean (*not absolutely neccesary)

[Ingredient] in the sub form could be a combobox based
on tblIngrediants.
[SubRecipe] in the sub form could be a combobox based on
tblRecipes... where tblRecipes *[IsSubRecipe] = True

Given that setup, you should be able to print recipes with just
the Ingredients and subRecipes listed... (*see Tip below)
Spaghetti
Salt
Water
Meat Balls
Spaghetti Sauce
etc.....

or with subRecipe "detail"

Spaghetti
Salt
Water
Meat Balls
Hamburger
Onion
etc....
Spaghetti Sauce
Tomatoes
etc....

*Tip: Given... that each Ingredient subform record should not have both
an Ingredient and a SubRecipe, you can reassemble both the Ingredients and
SubRecipes into one column for your reports. In a query behind your
report...
SomeName : =NZ([Ingredient],[SubRecipe].[Value])

Can't say as I've ever had to do this, but I'm pretty sure my setup
should work.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks for the help, but I think I didnt explain myself very well. Let
me give you a literal example, say the recipe is for "spaghetti and
meat balls" and the ingredients are as follows:

Spaghetti (cooked)
Meat Balls
Spaghetti Sauce

The problem is that Spaghetti isnt from the can, its hoem made so
along with the recipe for "Spaghetti and meat balls" in tbRecipe there
is also a recipe for "Spaghetti Sauce."

Does this clear it up?

Thanks agai nfor your help!

J,
tblRecipe should contain the name of the recipe, as well as any
ancillary info such as Servings, Instructions, etc...
It should have a unique key value... such as a RecipeID.
That's the One side of the relationship.
tblIngredients should list all the ingredients. It should have a
RecipeID (that relates to the correct Recipe) and an unique IngredientID
That's the Many side of the relationship.

If Recipe is related to Ingredients via RecipeID... then you can report
from the Recipe side of the relationship to Ingredients, OR from the
Ingredients to Recipe.
Example...
1 cup sugar
Apple Pie
Pecan Pie
Rhubarb Pie
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."










- Show quoted text -

I

 

[Tom]

What you are describing here is a bill of materials (BOM) - if you do
a search either in the news group or in general on those terms you
will get some ideas on how to proceed.

I have not had the opportunity to work with BOM database
structures...however, my understanding is that it is not a trivial
undertaking...

Good luck

Tom

 

[hor vannara]

What you are describing here is a bill of materials (BOM) - if you do
a search either in the news group or in general on those terms you
will get some ideas on how to proceed.

I have not had the opportunity to work with BOM database
structures...however, my understanding is that it is not a trivial
undertaking...

Good luck

Tom