Re: Hierarchical Relationship

In article <38bf0301.7784057_at_news.shuswap.net>, Gene Wirchenko <genew_at_shuswap.net> writes

>Bernard Peek <Bernard_at_shrdlu.com> wrote:
>
>>In article <89liln$7h0$1_at_news1.xs4all.nl>, John Smith
>><someone_at_microsoft.com> writes
>>>Hi,
>>>
>>>I am trying to make a Familytree in Access.
>>>In simplified form I have made
>>>2 tables: TblPerson and TblParents
>>>and 1 Querie: QryPerson&Parents.
>>>
>>>In TblPerson I have the basic properties of a person (ID, Name, sexe etc.).
>>>In TblParents I have 3 fields: Person_ID, Father_ID and Mother_ID
>>>
>>>TblParents is linked 1-to-1 with TblPerson by [TblPerson].[ID] and
>>>[TblParents].[Person_ID].
>>>The other 2 fields (Father_ID and Mother_ID) are fields that look up their
>>>values in TblPerson.
>>>
>>>QryPerson&Parents joins the 2 tables so that I get 1 recordset of a person
>>>including who his parents are.
>>>
>>>So far so good but there is one problem. I'd like to build in some
>>>validation. For example; one should not be able to select a distant
>>>offspring or a distant ancestor as a parent.
>>
>>You can probably do enough validation by looking at the date of
>>birth/death and making sure that nobody marries someone born more than a
>>hundred years earlier.
>
> That latter is not an absolute constraint.

True. It's probably better for the front-end system to query data rather than rejecting it.

>
>>Other validation rules could get a little tricky, I do know someone
>>whose family tree has a loop in it. It wasn't legal but it happened
>>anyway.
>
> How could a loop be possible? I think you mean something else:
>what?

Someone married their granddaughter.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk