Get = Find relationship -- help!

[lolitafontaine]

I inherited two interelated relationships from a former developer I can't figure out. For DB:Committees, we have an auxilliary DB:Members, which contains records for both the multiple members of each committee, but also the single chairman (sometimes there are 2 co-chairs).

DB:Committees has field com_GetChair, a calculation = com_CommitteeYear & "Chair".

DB:Members has mem_FindChair, also a calculation, indexed = mem_CommitteeYear & Right(mem_RoleOnCommittee, 5).

(The two CommitteeYear fields are different calculations in each DB. com_CommitteeYear = Left(com_YearRange, 4) & " " & com_CommitteeCode. com_CommitteeCode is a text field with an abbreviated committe name for reference, and the field yields something like "Advising 2006". mem_CommitteeYear is a bit more complicated. It calculates the same result, but relies on mem_Reply (to an invite to serve) to fill in the blank. I don't have these sort of calculation field references in the new DB I'm developing -- the reason I'm asking this question -- but it seems prudent to mention them here.)

These two fields (com_GetChair and mem_FindChair) yield what they look like they should on each record. com_GetChair = something like "2006 AdvisingChair" and mem_FindChair = "2006 AdvisingChair" or "2006 Advisingember" (if the record is for a member, and not the chair). Somehow, two relationships , called CHAIR and MASTER, isolates the Chair of the committee and returns that value on each of the member records.

Here are the relationships:

Relationship:CHAIR from DB:Committees - com_GetChair = mem_FindChair.

Relationship:MASTER from DB:Members - mem_CommitteeCode = com_CommitteeCode.

When I need to export, say, data for mail merge letters to each member which mentions the name of the chair (like "The chair of this committee, Professor So-n-so, invites you to serve on yadda yadda...")the export fields include mem_Chair, which is a calculation field = MASTER::Chair. This field does return the name of the chair for that committee, for that year, but I can't see how or why!

Now I have a new database in which I need to track members and chairs of a different type of committee, to produce letters like above. Basically, each member record for CommitteeX needs to be related to one record of the same CommitteeX subset.

Bob, Chair

John, Member

Bill, Member

Susan, Member

John, Bill, and Susan's records should be related to Bob's record by virtue of his being the chair of CommitteeX. The relationships explained above do that, but I can't recreate it nor can I figure out how to do it a different way.

Any help?