Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: Error with Relationship between tables

    Hi all. Thank you for taking the time to read my thread and hopefully provide an answer.

    I have five tables. Four tables have identical fields {id[pk,autonumber], strName, strIngOne, strIngTwo, strIngThree} and the fifth table has one field {strName[pk,unique}

    All of the str fields of each table should have a relationship back to strName in fifth table. My reasoning is strName is an item [say "Wall A"]. This item can have up to three things that make it up [say "Nail", "Screw", "Drywall"]. I want the fifth table to be a list of all the possible items that i can have. ["Wall A", "Nail", "Screw", "Drywall"] and be unique. The four main tables should references the item table to verify that the ingredients exist.

    As it stands I have the four tables and while they have referential integrity, when i look at the Items table it will only reference one table. i.e. it asks me to select a child table and row.

    I hope this post makes sense. I can post screenies or an attachment if necessary. Thanks again for the help.

    ~Deeno

  2. #2
    Join Date
    May 2010
    Posts
    601
    Deeno,

    ... Four tables have identical fields {id[pk,autonumber], strName, strIngOne, strIngTwo, strIngThree} ...
    That is a problem, You should not have four tables that are identical. They need to be all in a single table.

    Once you have this done, then everything else will be so much easier.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Oct 2010
    Posts
    4
    I guess I should clarify. Four tables {Baking, Frying, Grilling, Desert} contain the meals that can be made and a fifth table {Ingredients} would contain all of the possible ingredients to be used. I guess I am not thinking this through properly. The idea i had in my mind was that each of the primary tables would have fields for the necessary items to make the meal. Should there not be a foreign key relationship to make sure that the ingredient i'm putting into fields in fact exist in the table {Ingredients}?

  4. #4
    Join Date
    May 2010
    Posts
    601
    ... Four tables {Baking, Frying, Grilling, Desert} ...
    By doing this you are using the object (table) name as data. This should be in a field in the table

    What if you add an addition category like appetizers? You would have to add a table with your design. This indicated a issue with the design. Adding a new category should be as simple as adding a record into the categories table.

    You need at least these four tables:

    1) Meal (Recipe)
    2) Meal Ingredients (Recipe Ingredients) (juctio0n tbale between Meals and Ingredients
    3) Ingredients
    4) Category (lookup table {Baking, Frying, Grilling, Desert, etc}

    See:
    recipes template 2000 format
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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