Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    England
    Posts
    4

    Unanswered: one to many relationships help please

    Hi everyone.
    I am new to database so please be gentle. I know the very basics and thats it. I have been asked to design for practice purposes.My tutor gave me instructions as to what the database is about, she asked that I do another table that will relate to the one she as given.
    I have read books, searched the net but still cant seem to get the damn thing to work. I know how to do the relationship but it seems that I am not understanding the tables. These are the Fields that I was given.

    Name Title Craft Fee Location Area
    Smith A Mr Embroidery 2.50 Indoors 6

    I have tried to do a table that will do a one to many relationship using the fact that the agents can do more crafts, and different venues. I can do a one to one relationship.
    I have tried all sorts of different ways using the primary key in different fields but still no luck.

    What am I doing wrong?
    If anyone replies to this thread please understand that it will have to be explained in simple terms.

    Thank you

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    I hope I can help.

    Okay let me get this straight. You are needing to create a second table that relates to your first one right?

    Is there any paticular field your tutor told you to use, or do you get to pick which one so long as your relationship works?

    If you get to pick let me know which field you want to have a related table.

    If you can let me know the above information I will do my best to submit to you a detailed yet simple explanation of exactly what you need to do.

    Hope I can help,
    Jarvis

    P.S. What version of MS Access?
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're going to need more then two tables for this one. Consider all of the relevant pieces of information and their possible combinations:
    • Name: One name can be attached to any combination of crafts and locations, so it needs to be in it's own table.
    • Title: The title is going to be kept in the Name table, as it is always going to stay the same for any given name.
    • Craft: There could be any number of combinations of names, locations and areas for a craft, so it will be in it's own table as well.
    • fee: This may or may not be something you store in a table. If there is a static fee for every instance of a specific craft, then you may store it in the crafts table. (eg. embroidery ALWAYS costs 2.50 no matter when or where it is). If it is a variable fee, then you can leave this off and ask the user for the fee later.
    • Locations: Locations can again have any combination of names, crafts, and fees. So it will be in it's own table.
    • Area: Area will also be in it's own table because there could be any area for a given location.


    So your breakdown is as follows:

    tblAgent:
    agent_id (primary key)
    Name
    Title

    tblCraft:
    craft_id (primary key)
    craft_name
    fee (if it's static)

    tblLocation:
    location_id (primary key)
    name

    tblArea:
    area_id (primary key)
    location_id (foriegn key)



    Ok, now you have all the base information. To pull it all together you need another table:

    tblEvents:
    event_id (primary key)
    agent_id
    craft_id
    location_id
    area_id
    date?

    Through those id's you can pull all of the names or any other information you would like. As this is supposed to be a learning project, I won't go into that. This should be enough to get you started.
    Last edited by Teddy; 04-05-04 at 19:04.

  4. #4
    Join Date
    Apr 2004
    Location
    England
    Posts
    4
    Originally posted by Teddy
    You're going to need more then two tables for this one. Consider all of the relevant pieces of information and their possible combinations:
    • Name: One name can be attached to any combination of crafts and locations, so it needs to be in it's own table.
    • Title: The title is going to be kept in the Name table, as it is always going to stay the same for any given name.
    • Craft: There could be any number of combinations of names, locations and areas for a craft, so it will be in it's own table as well.
    • fee: This may or may not be something you store in a table. If there is a static fee for every instance of a specific craft, then you may store it in the crafts table. (eg. embroidery ALWAYS costs 2.50 no matter when or where it is). If it is a variable fee, then you can leave this off and ask the user for the fee later.
    • Locations: Locations can again have any combination of names, crafts, and fees. So it will be in it's own table.
    • Area: Area will also be in it's own table because there could be any area for a given location.


    So your breakdown is as follows:

    tblAgent:
    agent_id (primary key)
    Name
    Title

    tblCraft:
    craft_id (primary key)
    craft_name
    fee (if it's static)

    tblLocation:
    location_id (primary key)
    name

    tblArea:
    area_id (primary key)
    location_id (foriegn key)



    Ok, now you have all the base information. To pull it all together you need another table:

    tblEvents:
    event_id (primary key)
    agent_id
    craft_id
    location_id
    area_id
    date?

    Through those id's you can pull all of the names or any other information you would like. As this is supposed to be a learning project, I won't go into that. This should be enough to get you started.

  5. #5
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Well Explained

    Well Explained Teddy. Thanks for helping her.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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