Re: Hierarchical Relationship

From: Tim Fitzpatrick <>
Date: 2000/03/22
Message-ID: <8bb2g5$d8m$>#1/1

In article <89liln$7h0$>, "John Smith" <> wrote:
> 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
> 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
> values in TblPerson.
> QryPerson&Parents joins the 2 tables so that I get 1 recordset of a
> 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.
> I have 2 questions:
> -Is the method that I used any good or does somebody know a better
> to the problem.
> -Can somebody help me out with the validationrules.
> Corno Schraverus


Have you considered the possibility that a person can have more than two parents over time (e.g. can be adopted)? This could be resolved by having a TBL_INTERPERSONAL_RELATIONSHIPS table instead of your TBL_PARENTS. That way you could specify the various other relationships between two people (e.g. marriage, grandparents, cousins, etc) and introduce validation restricting the selections you mention. This might be a bit of overkill though.

Don't forget to prevent a person being their own parent...



Sent via
Before you buy. Received on Wed Mar 22 2000 - 00:00:00 CET

Original text of this message