Results 1 to 7 of 7

Thread: Junction tables

  1. #1
    Join Date
    Aug 2002
    Posts
    78

    Unanswered: Junction tables

    I understand the basic premise of the junction tables however I am not sure how they get populated etc. Is that done via code?

    I currently have
    tblDogs

    DogID
    DogName
    SireID
    DamID
    Champion
    Sex
    (other Misc information)



    As sire and dam (father andmother) are dogs I have them in the same table. I was trying to create a juntion table to relate the dogs as they can be fathers or mothers to multiple dogs.

    I understand concept of the junction table however I am missing how it is populated. I was hoping to use 1 table for the dogs as that will cut down on misspellings and updates to the dogs name (when they get a Title CH (for champion) is put infront of name etc) but I dont' get how to populate the junction table. Can anyone clue me in?

    I hope this made sense.

    Thank you

    thomas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're in luck, you don't need a junction table

    assuming that each dog as at most one sire and at most one dam, you already have the appropriate structure built in to your table

    each dog has two foreign keys, one to each parent dog, presumably each of these keys may be null, allowing you to insert a dog if you don't know its parents

    the only trick in populating the table is that when you add a dog, and want to identify its sire or dam, the sire or dam dog must be in the table already


    rudy

  3. #3
    Join Date
    Aug 2002
    Posts
    78
    thanx Rudy . . I appreciate the quick response. Yes you are correct. One dog can only have one Sire and one Dam however each of the sire and dams can be parents to multtiple dogs multiple times.

    But that is my issue. how do you populate and the table doing such? I am sorry I didn't explain myself properly.

    I don't know enough about the Foreign keys and have been having problems trying to get the information entered into the table not basing it on a query.

    Can you clue me in to how this is done? is it via code (if so what might that be) or something is it something else? (IF YOU HAVE TIME and are willing.) If not that is cool as I am sure most people here are as busy if not busier than myself.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that is the beauty of hierarchies, each parent can have multiple children

    you have two hierarchies, parent-as-sire and parent-as-dam

    the sql to load the table is easy --

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 1, 'Red', null, null )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 2, 'Yellow', null, null )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 3, 'Orange', 1, 2 )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 4, 'Blue', null, null )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 5, 'Green', 4, 2 )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 6, 'White', null, null )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 7, 'Beige', 6, 2 )

    insert into tblDOgs ( DogID, DogName, SireID, DamID )
    values ( 8, 'VeryBeige', 6, 7 )


    see if you can figure out who is the sire and dam of each dog


    rudy

  5. #5
    Join Date
    Aug 2002
    Posts
    78
    OK Answers :

    Red's parents aren't listed
    Yellow's parents aren't listed
    Oranges parent are Red and Yellow
    blues parents aren't listed
    Green's parents are blue and yellow
    White's parents aren't listed
    Beige parens are White and Yellos
    Very Beige's parents are White and Beige


    Yes?

    I get this part as I understand the table etc. I even understand how to enter the information into the table via SQL. I am trying to get it to work on a form however.

    So my senario is this:

    Starting off with and empty table I want to build a form


    I have a control named
    Dog

    second named Sire

    third named Dam

    What do I need to do for it to look at the table and see if the dog exists then if not put it into the table with the relating parents?

    What code or ???do I need to connect to a submit button.

    I believe I can get the sire control to bring up the dogs names based on being a dog (male) and the dam control I can bring upbased on being a bitch (female).

    Kinda hard to get used to but that is the terms the breeders/showers use.

    So for the first record it would have three new dogs added to the table but how do I get the references DOGID to match the Sire (SIREID)and Dam (DAMID)

    Am I a complete idiot or am I missing something?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is where you and i part company, i can't do forms

    i would strongly recommend that you consult a microsoft access tutorial, because what you need to do, in connecting a form to your table, is very straightforward, but i'm sorry, i just don't know it

    after taking your tutorial, if you still have trouble, post another question here (the access forum), but make sure the title is "connecting forms to tables" or something similar, because anybody still reading this thread is/was interested in junction tables

    good luck


    rudy

  7. #7
    Join Date
    Aug 2002
    Posts
    78
    Thank you very much for your time it is muchly appreciated.

Posting Permissions

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