Query Question

[starhannes]

Hey again

Ok this is quite complicated to explain.. hope i get my question accross!

i have 3 tables.


1) Recipe

Name      ID
Pizza     1
Pasta      2

2) Ingredients

RecipeID(linked to 1) Ingredients    Amounts
1                        Pizza             1
2                        Pasta-grams    500
2                        Sauce-grams    400

3) Fridge

Ingredients        Amount
Pizza                 2
Pasta-grams        600
Sauce-grams         100

Now i want to make a query: What recipe can i make with the food that i have in my fridge.
-> The Pasta recipe should not be shown at all!

How do i do this? i cannot use Fridge.Amount > Ingredients.Amount because then i will still see

Recipe Pasta -> because i have sufficient Pasta (600g when 500g required).

Thanks

[maximus]

How is the table Fridge linked to the other two. Not showing an item is simple. Suppose if I prepare a query combining Recipie and ingredients and in the criteria of ID put this criteria <>2 then pasta is not shown at all. But I want to understand the logic of the whole operation. At what condition will an item be not shown.

This is what i assume you want to do.
1) Have a table to record food Item Primary Key ID
2) Have another Table Receipe which stores the ingredients.
3) Now at any point the user will give some quantities of various ingredients in his fridge and the query will display all possible reciepes that the user can make with it.


If I am right let me know .

[starhannes]

Yes Maximus more or less if i understand you correctly, thats what i wanna do.

please see file http://www.2shared.com/file/12739134.../database.html

i hope you understand how they are linked etc

[maximus]

sorry I have ms office 2000 here in the office can you upload a copy of the db in ms access 2000 format.

[starhannes]

Sure:

http://www.2shared.com/file/12740316.../database.html

thanks!

[starhannes]

is this so hard to do??

[maximus]

No I just seemed to have lost track of you problem will look into it today. Thanks for the reminder.

[starhannes]

thanks alot maximus i was afraid i was stuck !! :P

[starhannes]

eemmm should i throw out another reminder?? im kinda on a deadline thks alot

[maximus]

No need sol will folow in 2 hrs

[maximus]

Hi I have done some thing and before I release iti want to know some thing when i see the ID Field of Fridge is Linked to Ingredients of ReceipeIngredients.

Now my understanding of you database is Simple food intems and its reciepe are stored in tables User supplies the ingredients that are there in his fridge and he is given a selection of the most likely dishes he can make with them. So basically here is what I have to do find out all the reciepies that you can make with the ingredients in the fridge not considering the quntities and after selecting them out rule out the dishes that cannot be prepared due to insufficent ingredients.

[starhannes]

Hi I have done some thing and before I release iti want to know some thing when i see the ID Field of Fridge is Linked to Ingredients of ReceipeIngredients.

Now my understanding of you database is Simple food intems and its reciepe are stored in tables User supplies the ingredients that are there in his fridge and he is given a selection of the most likely dishes he can make with them. So basically here is what I have to do:
1. find out all the recipes that you can make with the ingredients in the fridge not considering the quantities and
2. after selecting them out rule out the dishes that cannot be prepared due to insufficient ingredients.

Yes exactly thats what i want to !

You want to know why the ID field of fridge is linked to Ingredients of RecipeIngredients??

To be honest, i needed to link the Fridge to something and i didnt know what... So i made the Fridge also a "list of all ingredients", where recipeIngredients uses the "lists of all ingredients" to make recipes.
Now, of course there will be many ingredients in the Fridge table that are quantity 0 (when they are not in the fridge), but that doesnt affect the link with RecipeIngredients table...


Hope that is clear?

thanks alot by the way!

[starhannes]

would you be able to send me what you have done so far please?

[starhannes]

could i bump this please?