Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Relationships (2k)

    I have a table of people, Primary key PersonID. This contains family members Father, Mother, Grandfathers, Grandmothers etc.
    Clearly my father's father is my grandfather.
    Assuming I provide my father (& Mother's) ID's manually, how can I get Access to copy my father's father ID into my grandfather field automatically and so on back for several generations.
    Clearly there is a fixed relationship so it must be programmable but I can't get my head round it.
    Thanks in anticipation.
    Mike

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table Relationships (2k)

    I wrote a Dog Registration system some time back and it had similar requirements.

    All I did was to put the dog's number into a field called DogID and the dog's sire number into the SireID on the same record and the dog's Dam number into the DamID also on the same record.
    So one record had a dog's Sire and Dam number in it.

    In this way I had a way of tracing the family tree backwards from the dog record.

    I did not have a requirement of going forwards (ie. tracing the children and their children etc., that's if you call dogs children).

    HTH

    Pat

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Relationships (2k)

    Thanks Pat but with 900+ entries I wanted Access to do the work automatically , once I had made the basic link between son and father

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Relationships (2k)

    Sorry, but I don't understand why you would have a grandfatherID field at all. At most you would need a field for FatherID and MotherID, since each of those related records would have their own FatherID and MotherID in them. So your father's FatherID is your paternal grandfather and your mother's FatherID is your maternal grandfather. You don't need the key in your own record because you can reach it through your father's or mother's record. Am I missing something?
    Charlotte

  5. #5
    Lounger
    Join Date
    Jan 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Relationships (2k)

    Thanks Charlotte,
    Maybe its me that's missing something. I am replying immediately on reading your post. I shall think about it overnight, but I can't see immediately how I can write a query to display My Father's details and his father's details unless his father's ID is in my record. Yet something in the back of my mind says you are probably right, but I can't see how to display father and grandfather (or more precisely how to set up a query which will do that. Hope you are right as it will simplify the whole db considerably.
    Many thanks for your time
    Mike

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Relationships (2k)

    If you have a FatherID in your record, you add another instance of the People table joined on FatherID in People to PersonID in People_1. The FatherID in the People_1 table represents your grandfather.
    Charlotte

  7. #7
    Lounger
    Join Date
    Jan 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Relationships (2k)

    Thanks Charlotte,
    As I was thinking after your earleir email I realised that might be the solution. I have to say that the various occasions when you add a second instance of a table are non-intuitive.
    Further in this example if I am going back several generations (>5) the query becomes a little cumbersome. Thank you anyway. I shall play with your solution and see how it works out. I had also used a tree control to show the various generations, though I programmed it manually. I hope to get some spare time once I have finished teaching this 12 week course and then I will experiment a bit more.
    Many thanks for all your time and help. Something like not seeing wood for trees...I may be back!
    Best wishes
    Mike

Posting Permissions

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