Need Query Help!

[rad1182]

Hello everyone. I was hoping someone could provide me some Query help.

Let's say I have two tables:

TableMyIngredients (contains one field)


FieldMyIngredients - lists ingredients that I have in my possession

TableDishes (contains two fields)
FieldIngredients - lists all ingredients (in one field ) needed to make the dish
FieldDish - lists the name of the dish

These aren't my actual table and field names, but I thought these would make it easier to explain my situation. Anyway, due to an extreme time crunch when I was asked to create a database for this project, I ended up listing all of the ingredients associated with a dish in one field in TableDishes. Terrible, terrible design, I know. And I don't really have the option of going back and creating a proper database at this point.

Would it be possible to write a Query that will list all the dishes that I have two or more ingredients for?

For example

If TableMyIngredients list the following:
FieldMyIngredients
Beef
Carrots
Potatoes
Olive Oil

And TableDishes lists the following:
FieldDish - FieldIngredients
Beef Stew - Beef, Carrots, Potatoes
French Fries - Potatoes, Olive Oil
Burger - Beef, Bread, Cheese
Mash Potatoes - Potatoes, Butter

I would like for the query to return "Beef Stew" and "French Fries" because two or more of the ingredients that make up that dish are listed in TableMyIngredients but not "Burger" and "Mash Potatoes" since only one of the ingredients that those consist of is listed in TableMyIngredients.

Make sense? Thanks in advance for any help.

Cheers,
Ryan

[hertfordkc]

Hello everyone. I was hoping someone could provide me some Query help.

Let's say I have two tables:

TableMyIngredients (contains one field)
FieldMyIngredients - lists ingredients that I have in my possession

TableDishes (contains two fields)
FieldIngredients - lists all ingredients (in one field ) needed to make the dish
FieldDish - lists the name of the dish

These aren't my actual table and field names, but I thought these would make it easier to explain my situation. Anyway, due to an extreme time crunch when I was asked to create a database for this project, I ended up listing all of the ingredients associated with a dish in one field in TableDishes. Terrible, terrible design, I know. And I don't really have the option of going back and creating a proper database at this point.

Would it be possible to write a Query that will list all the dishes that I have two or more ingredients for?

For example

If TableMyIngredients list the following:
FieldMyIngredients
Beef
Carrots
Potatoes
Olive Oil

And TableDishes lists the following:
FieldDish - FieldIngredients
Beef Stew - Beef, Carrots, Potatoes
French Fries - Potatoes, Olive Oil
Burger - Beef, Bread, Cheese
Mash Potatoes - Potatoes, Butter

I would like for the query to return "Beef Stew" and "French Fries" because two or more of the ingredients that make up that dish are listed in TableMyIngredients but not "Burger" and "Mash Potatoes" since only one of the ingredients that those consist of is listed in TableMyIngredients.

Make sense? Thanks in advance for any help.

Cheers,
Ryan

Yes, you probably can do what you propose, but I'd bet it could be done faster by straightening out the tables. Perhaps a third table, Recipes, which ties together the dishes and ingredients.

[pbaldy]

Gosh, there's somebody with the exact same problem elsewhere, but with faculty and publications, and his name is Ryan too!

You already know the design is going to cause major problems, and this is one of them. Is the homework being due the reason you can't fix it? If I'm the instructor, I'm marking down for the normalization issues anyway.

The only way that comes to mind would involve a custom function. It would use the Split() function to parse out the ingredients. Within a loop of the resulting array it would look for each item in the ingredients table and accumulate a count of those found. The function would return that count, so you could have a criteria on that.

[orange]

Not a sound data structure.

TableDishes
id (PK)
DishName

 TableIngredients
Id (PK)
IngredientName

TableDishIngredient
id (PK)
DishId (FK) refers to TableDisID
IngredientID (FK) refers to TableIngredients id

You need a Junction Table  to enable a Dish to have multiple ingredients.

Consider TableIngredients
1 Beef
2 Carrots
3 Potatoes

TableDishes
1 BeefStew

TableDishIngredient
1 1 1
2 1 2
3 1 3

[rad1182]

Busted...haha. I wish it was as simple as homework being due. The biggest constraint right now is time. There's somewhere around 2500 entries in the "Dishes" table and each "dish" has anywhere from 10 to 20 "Ingredients". I don't believe I have the time to create a proper one-to-many relationship between dish and ingredients, unless there's a quick way of doing that I don't know about.

I was thinking more about this...

If I use the following criteria (in a query) on the FieldIngredients:

Like "*" & "Ingredient Name" & "*"

It will return all dishes with that ingredient. If I do that for all of the ingredients on TableMyIngredients, I can take each individual results, export it to Excel, sort it by dish name...and all dishes that are listed more than once are the dishes I'm looking for. But that would involve typing each individual ingredient in TableMyIngredient into a query criteria (or creating a combo box query which is still time consuming).

Is there a criteria I can write using the "Like" operator that will check for every entry in a given field?

I tried the following but it didn't work:

Like "*" & "[TableMyIngredients]![MyIngredients]" & "*"

Thanks for the input.

[hertfordkc]

put the entries into another table, which could be more reasonably managed.

[orange]

All responses have highlighted your table structure issue. That's the basic issue.

No real quick way to do this.
Quickest is probably a Form with 2 combo boxes

Dishes and Ingredients and a Button to Add a record.

Select a Dish, then Select an appropriate ingredient, click ADD
Go to the next ingredient and repeat
when done with that Dish, proceed to the next dish

When things don't work -- it's more helpful if you tell us an error 999 resulted or something. Doesn't work isn't helpful.

[rad1182]

When things don't work -- it's more helpful if you tell us an error 999 resulted or something. Doesn't work isn't helpful.

When I created a query based on TableDishes and put the below statement in the criteria for FieldIngredients, the query returns zero entries. No error messages.

Like "*" & "[TableMyIngredients]![MyIngredients]" & "*"

[orange]

Like "*" & "[TableMyIngredients]![MyIngredients]" & "*"

is a literal string.

I think you want
Like "*" & MyIngredients & "*"

No quotes around MyIngredients