Results 1 to 9 of 9
  1. #1
    rad1182 is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2011
    Posts
    10

    Need Query Help!

    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

  2. #2
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    IMHO, your time would be better spent straightening out the design.

    Quote Originally Posted by rad1182 View Post
    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.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Not a sound data structure.
    Code:
    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

  5. #5
    rad1182 is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2011
    Posts
    10
    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.

  6. #6
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    By now you should have created the vba and query necessary to

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

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  8. #8
    rad1182 is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2011
    Posts
    10
    Quote Originally Posted by orange View Post
    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]" & "*"

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Like "*" & "[TableMyIngredients]![MyIngredients]" & "*"
    is a literal string.

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

    No quotes around MyIngredients
    Last edited by orange; 11-30-2011 at 01:49 PM. Reason: edit

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums