Variable Relationships

[bankofbermuda]

I have 2 related databases, Clients and Parties in which several parties are related to one client by a specific relationship titled Mother, Father, Son, Daughter.

In the Party database is a Score field. In the Client database is a score field referencing each relationship i.e. Mother:Score. or Father:Score, Son:Score, Daugher:Score.

My objective is to select the relationship which has the highest score so that the other fields in the Party database may be referenced by the Client database for the highest scoring relationship. Is there a way to accompish this??

[BobWeaver]

Make a new relationship based on a calculated field called HiScoreRel (in the clients file) with this formula:

case (

Mother::Score = Max(Mother::Score,Father::Score,Daughter::Score,Son::Score), MotherRecordID,

Father::Score = Max(Mother::Score,Father::Score,Daughter::Score,Son::Score), FatherRecordID,

Daughter::Score = Max(Mother::Score,Father::Score,Daughter::Score,Son::Score), DaughterRecordID,

SonRecordID)

...where MotherRecordID, FatherRecordID, DaughterRecordID and SonRecordID are the unique record IDs for the respective records.

[bankofbermuda]

Bob, Worked great!! Thanks for the reply.

Chris