Recursive data

[jvrsoto]

I am building a database that will include ingredients and subingredients (subingredients can have more subingredients).

Ex.
ID Ingredient / SubIngredients
1 Butter Finger / Peanut Butter
2 Peanuts
3 Salt
4 Peanut Oil
5 Peanut Butter / Peanuts
5 Peanut Butter / Salt
5 Peanut Butter / Peanut Oil

How can I make a query, or function where I can just select Butter Finger as my Ingredient but it knows that it includes Peanut Butter, and Peanut Butter includes Peanuts, Salt, and Peanut Oil?

Of course Salt and Peanuts might be subingredients or main ingredients in another formula (so a parent Id will not work).



Thanks

[hertfordkc]

intuition here: you need to have a table of intermediate ingredients and another table of basic ingredients, e.g salt, flour....
I'll think about it some more.

[jvrsoto]

Thanks for the response. But here is my thing, Peanuts could be a main ingredient, or a subingredient at second or third level, etc.

[RayMilhon]

What it sounds like your looking for is an object oriented Data Structure not relational. Don't think this is possible with Access. SQL Server 2008 includes the Hierarchical data structure which may work but I have 0 experience with this structure. You could also look at Cache' which is an object oriented Database at least it used to be haven't used or seen it in over 3 years.

[RayMilhon]

By the way You can check out Cache' @ www.intersystems.com