D.I.V.O.R.C.E.

[Gogargirl]

I have a database in which the records are people, some of whom are linked to one another. Each record has 3 different ID fields signifying whether the person is a Host, a Guest or a Spouse. These are text fields which have number codes.

There are then self-joins to match the IDs (HostGuest is the r'ship for HostID::GuestID, HostSpouse is the r'ship for HostID::SpouseID etc.) so that if I am in John Smith's record I can see he is the host of Bob Jones in the field HostGuest::ExecName.

That's fine. Now when I'm in John Smith's record I also want to see if his guest Bob has a wife. So I created a calculation field Hostguestspouse where the result is the related field HostGuest::SpouseID. This field is then used in another relationship 'ShowSpouse' where the match fields are Hostguestspouse::HostID. So now, when I am in John Smith's record I can see Betty Jones in the field ShowSpouse::Exec Name.

The problem arises when John Smith has more than 2 guests. I can easily see all of the guests in a portal displaying HostGuest::ExecName, but I can only see the wife of the first guest in a portal for ShowSpouse::ExecName. Is this because ShowSpouse can only reference the first record where HostGuest::SpouseID matches Host ID? I've tried all kinds of things and can't get it to work.

Sorry this seems a bit bigamous.....

[Chuck]

If I understand correctly (and I'm not sure I do), create a Spouse field in the database that is a calculated text field set to the spouse's name. Show this field in the portal of guests.

Chuck

[Gogargirl]

That's it exactly Chuck - many thanks!