Have I Painted Myself into a Corner?

[panache]

Hello. Here's what I've done.

I work for a residential school. We have, among others, a student info database ("Students") and a database of parents/school contacts/emergency contacts, etc ("Contacts.") Each record in Students is given our ID number, called "MI#." Likewise with each record in "Contacts," so logically MI# is the key field between the two.

Now here's my question. I want to be able to display some of the info in Contacts in Students, namely the names and phone #'s of the related records. So, I define a relationship between the two using MI# as the key field. In Students, I place the related field "nameFirst" which lives in Contacts, and then I see the problem: Each student in Students can have many related records in Contacts (for instance Joe Shmoe can have a father record, a mother record, a school contact record, etc.). On my layout in Students I want to display father's first name, mother's, etc., but of course the relationship is picking up the first occurrence because there's only one field in Contacts that holds a first name (nameFirst). How do I enable the correct display in Students? That is to say, how do I get it to display father's first name when I want father's, mother's when I want mother's, etc.

I tried fiddling with the "sort" options in the relationship to no avail. I'm sorry if this is a blatant noobie question, but I can't figure it out and I can't find the answer in my books. Thanks in advance, I'm pulling out what's left of my hair.

Version: v6.x

Platform: Mac OS X Panther

[Damocles]

Have you tried using a portal to display all related records?

Paul

[RalphL]

Consider using calculated keys. In Student use fields Father = MI# & " Father", Mother = MI# & " Mother", School contact = MI# & "School contact", etc. In Contact use a field Contact = MI# & " " & Contact_Type. Make relationships with these files and use them to display the desired data.

[Ender]

In the education solutions I've worked on, I have found it useful to have a Family file that has one record per household. This is a good way for adding siblings and a way to share certain contact information.