Identifying Siblings

[dovcamp]

I am creating a database which contains student contact information for a school (FM 3).

When I bring up a specific student I want to be able to show all of that students siblings that go to the school and are in the database.

I know this can be done by creating a calculation field that combines the last name and the last 4 digits of the students phone number to create a "Family ID." Then the "Family ID" would be set up in a relationship to pull out the siblings into a portal.

The problem I am having is that the way the database is set up is that each student doesn't have his own phone number, but rather there are home phone number fields for each one of his parents. So what I need is to be able to have match up students that are siblings based on the fact that any of their parents phone numbers match with any other students parents phone numbers.

Thanks.

[LiveOak]

Just build a self relationship in the Students file matching Parent's Phone to Parent's Phone. You can then place a portal base upon this relationship on a form view in the Student file and see the other siblings.

-bd

[dovcamp]

That's exactly what I did, but the problem is that there could be up to 4 different parent's phone numbers. So it needs to match any other students where any of their parent's phone numbers are the same as the the student being viewed.

[Kurt Knippel]

quote:

---

Originally posted by Dov Campbell:

That's exactly what I did, but the problem is that there could be up to 4 different parent's phone numbers. So it needs to match any other students where any of their parent's phone numbers are the same as the the student being viewed.

---

Correct me if I am wrong here: any given student can only have 2 parents? Or can they have multiple "parent" records? (i.e. parent, grandparents, step-parent, guardians, etc).

I would not link via Telephone #, since as soon as they move all of your links break. I would have a unique ID for each parent record and link via that. Do not allow the user access to this field however.

Anyway if they only have say 2 parents, then you could simply put those 2 Parent::ParentID_key values into the Student::ParentID_fkey field.

If the student can have some unknown number of parents, then perhaps a join file is in order. At a minimun the join file has the StudentID_key and the ParentID_key in each record. This way you can have zero or more parents for each student.

[dovcamp]

quote:

---

Correct me if I am wrong here: any given student can only have 2 parents? Or can they have multiple "parent" records? (i.e. parent, grandparents, step-parent, guardians, etc).

---

They can have up to 4 parents. The parents' phone numnbers are not in separate records, but rather just fields in the database (for example "Parent 1 Phone Home."

I don't have a lot of experience with advanced functions so please detail any explanation. Thanks.

[Kurt Knippel]

quote:

---

Originally posted by Dov Campbell:

They can have up to 4 parents. The parents' phone numnbers are not in separate records, but rather just fields in the database (for example "Parent 1 Phone Home."

---

I cannot condense years of experience down to a few sentances, so perhaps explaining this is not possible.

Relational database theory is an old and well documented discipline and you will find lots of books on it on Amazon.com, at your local bookstore, even the library. Start there and learn the basics of relational database design. This will clarify some of what I have said to do.

The problem with relating your students to parents via phone number is that phone numbers change. As soon as they do your relational links are broken. This is why I recommend an auto-entered ID, which the user NEVER sees or has any kind of access to. Use this for linking your records together.

At the simplest your students would be linked to the parent by the ParentID_key which is this ID field for the parent. Store this in the ParentID_fkey in the Student file. You can store as many of the parent IDs as you need to in this field, each on a seperate line.

Now again do some reading on relational database theory and all of this will be come clear.