Table Relationships (2k)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote