Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Location
    Boston, MA.
    Posts
    4

    Unanswered: Default Value in Access Table

    Hi all,

    I'm new to this forum. My question might be pretty basic, but I've not yet found a way to solve a particular issue.

    I'm creating an Access db (2000 format) in which I'll be storing recipes. In the table that I am using to store ingredients, I have a composite key comprised of recipeID & ingredNo ... recipeID is an autonumber and my plan for ingredNo is for it to be an autonumber that restarts for each recipeID. For example:

    recipeNo ingredNo ingredient
    2 1 frankfurts
    2 2 1 cup apple jelly
    2 3 1/2 cup plain mustard
    3 1 green split peas
    3 2 ham
    3 3 cabbage
    3 4 1 tbl vinegar
    3 5 butter
    3 6 salt and pepper

    Can anyone tell me how to acomplish this? Thank you.

    The image, attached, shows how the tables are linked.
    Attached Thumbnails Attached Thumbnails tatf001.gif  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why do you store the recipeID in the Ingredient table? This means that you cannot use the same row from the Ingredient table in more than one recipe.

    I would use one table for the recipes, one table for the ingredients and a third table that would hold the primary keys of both tables (possibly with a Quantity column) to create a many-to-many relationship between recipes and ingredients (a recipe uses more than one ingredient and an ingredient is used in more than one recipe).
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Location
    Boston, MA.
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    Why do you store the recipeID in the Ingredient table? This means that you cannot use the same row from the Ingredient table in more than one recipe.

    I would use one table for the recipes, one table for the ingredients and a third table that would hold the primary keys of both tables (possibly with a Quantity column) to create a many-to-many relationship between recipes and ingredients (a recipe uses more than one ingredient and an ingredient is used in more than one recipe).
    Hi Sinndho. Thanks for the suggestion. I did consider that early on, but decided against it for ease-of-input reasons. There'll be some older, non-computer savvy users who will be more apt to use the app if they can freely type the ingredients. I just don't want to have them freely type the ingredNo. Thank you, though.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't see you point. According to me data input has nothing to do with the organisation of the database. However, if you really want to do it your way, you could try something like this:
    Code:
    Function GetIdNumber(lngRecipeId As Long) As Long
    
        GetIdNumber = Nz(DMax("IngredNo", "INGREDIENT", "recipeID = " & lngRecipeId), 0) + 1
        
    End Function
    If there is no row in INGREDIENT with lngRecipeId in the recipeID column (no match), the DMAX function returns Null which is converted to zero by the Nz function and GetIdNumber returns 1 (0 + 1). If matching rows already exist in INGREDIENT, DMax returns the highest value of IngredNo in the matching rows and GetIdNumber returns this number plus one, i.e., the next number in the sequence.
    Have a nice day!

  5. #5
    Join Date
    Apr 2010
    Location
    Boston, MA.
    Posts
    4
    Thanks. I'll give that a shot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •