Results 1 to 7 of 7

My recipe database: how to make searchability (filters) more functional?

  1. #1
    mechitar is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2019
    Posts
    1

    My recipe database: how to make searchability (filters) more functional?

    Hello,

    I taught myself Access so I could use it for one thing: to create a database of recipes. To be clear, the recipes (instructions for cooking) aren't in the database; rather, it consists of names of many recipes, along with metadata for each one to allow me to search for recipes that meet certain criteria.



    So far this has worked pretty well, but there are two types of database searches I'd like to be able to do that I haven't been able to make work.

    First: I want to be able to filter the recipes to list only the ones that contain all of the ingredients I'm interested in. For example, I might want a list of all of the recipes in the database that contain parsley AND lemon AND tomatoes. But, I can't figure out a (remotely easy) was to filter by more than one ingredient at a time. This is what I did: I created a field called "Key Ingredients". For each recipe listing I populated this field with the key ingredients in it, each term separated by a comma (no spaces). Then I perform my search by selecting the column for the field, selecting Text filters -> Contains..., and keying in the one key ingredient I'm interested in. Apparently Boolean operators aren't available for this kind of filter, so I can't do the search for recipes that contain ALL of multiple ingredients. Or is there? Or should I have gone about this in a whole different way?

    Second: A similar problem to the first. Another field I've created is called "Diet Compatibility". Unlike Key Ingredients, which allows entry of any text, this field has defined lookups that are selected with check boxes: "Vegetarian", "Vegan", "Gluten-Free", and the like. I want to be able to view only the recipes that have all of Diet Compatibility types I'm interested in at a given time. For example, I might want a list of all recipes that are BOTH vegan AND gluten-free. Again, it doesn't seem like I can do this kind of Boolean operation within a single field. I know I can create separate fields for every diet compatibility type I want and do my filtering using those multiple fields, but I'd rather not do it in this cumbersome way. Anyone know how I can do this the way I want to?

    Thanks in advance for any help!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,540
    Or should I have gone about this in a whole different way?
    I would say so. That would require you to loop through every comma separated value, looking for EACH of the key ingredients and only return the recipe if ALL values are found.

    Ideally you would have tblRecipes and a table of ingredients and a junction table to join them.
    For "Tomato Soup" recipe [tblRecipe].[RecipeID] = 1 you require ingredients 2,4,6,8 from tblIngredients

    tblRecipeIngredients (junction table)
    riID RecipeIDfk IngredientIDfk
    1 1 2
    2 1
    4
    3 1
    6
    4 1
    8
    This all refers to the concept of normalization, which I suspect you might benefit from researching.
    this field has defined lookups
    If this means you have lookup fields in a table, it's not recommended. Have to run, but a quick read of issue 2 seems like you need a similar approach as issue 1.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,205
    Welcome to the forum...


    Quote Originally Posted by mechitar View Post
    For each recipe listing I populated this field with the key ingredients in it, each term separated by a comma (no spaces).
    The problem is that your table design(s) are not normalized.
    Your design violates 1NF (first normal form)


    The First Normal Form, or 1NF, is the very lowest, basic arrangement of fields in a table. If your table is not in 1NF, then it isn't really a table. Sadly, many novice databases are not even in 1NF.

    Definition
    A table is said to be in First Normal Form if:
    1) there is no row or column order
    2) each row (record) is unique
    3) each row by column value (field) contains exactly one value
    4) there are no repeating columns


    Because a field must have a single value, it cannot contain a list or compound value (but you have a list - example: parsley,lemon,tomatoes).



    I would suggest stepping back and re-designing your tables......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    A common approach to search for multiple criteria in a single field utilizes a multi-select listbox on a form http://allenbrowne.com/ser-50.html. However, your requirement for a recipe to match all of the selected ingredients is a twist. You will want a table of Ingredients to use as RowSource for the listbox

    Though not normalized, CSV can be dealt with. Consider:

    Code:
    Function MatchIngredients(strIngredients As String) As Boolean
    Dim varItem As Variant
    MatchIngredients = True
    With Forms!FormName.lbxIngredients
        If .ItemsSelected.Count <> 0 Then
            For Each varItem In .ItemsSelected
                If InStr(strIngredients, .ItemData(varItem)) = 0 Then
                    MatchIngredients = False
                    Exit For 'only need match to fail for one ingredient
                End If
            Next
        End If
    End With
    End Function
    Then call function in query:
    SELECT * FROM Recipes WHERE MatchIngredients([KeyIngredients]) = True;

    However, a listbox with a very long list can be unwieldy. So an alternative is to loop through a recordset of ingredients selected by a yes/no field in Ingredients table. I assume you are the only user of the database so there will be no conflicts with other users setting value of yes/no field.
    Code:
    Function MatchIngredients(strIngredients As String) As Boolean
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Ingredients WHERE MatchIng = True")
    MatchIngredients = True
    Do While Not rs.EOF
        If InStr(strIngredients, rs!IngredientName) = 0 Then
            MatchIngredients = False
            Exit Do 'only need match to fail for one ingredient
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    End Function
    Even with a normalized data structure, requirement for retrieving "only the ones that contain all of the ingredients I'm interested in" is not a simple task and will likely involve VBA (at least I cannot figure out a query-only approach).

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    Last edited by June7; 04-21-2019 at 03:44 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    pcplumber is offline Novice
    Windows 8 Access 2003
    Join Date
    Apr 2019
    Posts
    1
    Quote Originally Posted by Micron View Post
    I would say so. That would require you to loop through every comma separated value, looking for EACH of the key ingredients and only return the recipe if ALL values are found.

    Ideally you would have tblRecipes and a table of ingredients and a junction table to join them.
    For "Tomato Soup" recipe [tblRecipe].[RecipeID] = 1 you require ingredients 2,4,6,8 from tblIngredients

    tblRecipeIngredients (junction table)
    riID RecipeIDfk IngredientIDfk
    1 1 2
    2 1
    4
    3 1
    6
    4 1
    8
    This all refers to the concept of normalization, which I suspect you might benefit from researching.

    If this means you have lookup fields in a table, it's not recommended. Have to run, but a quick read of issue 2 seems like you need a similar approach as issue 1.
    Hello! I am new to this forum and know very little. Thank you for having me.

    Just curious. Is it possible to have have a sub form with a field for the ingredients. Then, have all the ingredients collected and sent to one field i.e. a memo box. Then, the recipes that contain the same ingredients can be retrieved with a boolean search? Or, can a boolean search be used in another way?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,540
    Having trouble picturing that situation. I wouldn't even want to think about it as there's no reason to pervert what would be the proper approach (which was mentioned in posts 2 and 3) in order to 'Frankenstein' some other kind of convoluted approach. If having a properly constructed set of tables isn't of interest to you, then there's not much I can do to help. Sorry.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Not recommending but it is possible. That search code is described in post 4.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 01-21-2019, 01:09 PM
  2. recipe file database help to start
    By JohnnyS in forum Access
    Replies: 6
    Last Post: 01-09-2017, 03:27 PM
  3. Query which filters the database
    By acces in forum Queries
    Replies: 4
    Last Post: 07-08-2015, 11:47 AM
  4. Database: From recipe to shopping list
    By schwabe in forum Database Design
    Replies: 3
    Last Post: 05-17-2014, 06:48 PM
  5. Replies: 2
    Last Post: 03-29-2014, 01:56 PM

Tags for this Thread

Posting Permissions

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