Connecting Family and Friends relationships in Access

[RockKandee]

I am using Access 2013 in Windows 8

I have a Student table that holds all students with their contact and personal information. Each student has a primary key auto number.

I have students that have relationships with other students that I want to track.

I do not care about tracking non student relationships (ie; I don't care who a 14 year old student's mother is unless she also takes a class.)

If father, mother, 2 sons, 1 daughter, and a friend of the family take a class, I want to see these connections.

I want to see these connections when:
I view a student's record.
or
Put together a mailing list. (same address or not)

I am not sure the best way to set this up.

I was thinking about making a Relationship table with:

Student Id (from the Student table)
Relationship (e.g. Spouse, Siblings, Significant other, Friends)
Student Id (from the Student table)

If this is the best route to take....

Would I

John - spouse - Jenny
Jenny - spouse - John
Jenny - parent - Bill
Bill - child - Jenny
Bill - child - John
John - parent - Bill

even though that would duplicate the same information.

If I only entered John - spouse - Jenny
Jenny's relationship to John wouldn't show up when I use the first field to retrieve info. I am not sure how else to retrieve the info unless I can always use the first field when referring to a specific student.

Any thoughts on this?

Thanks :D

[ADezii]

1. You could add a new Relationships Table with 3 Fields:
   1. [StudentID] - relates back to Students Table in a MANY to 1 Relationship.
   2. [Relates To] - Numeric (LONG) that contains the StudentID to whom this individual is related.
   3. [Type] - LONG that relates to a Types Table (tblType) in a MANY to 1 Relationship.
2. tblType consists of a AutoNumber, PK Field that relates back to the [Type] Field in the Relationships Table.
3. A [Type] Field (TEXT) that contains the Type of Relationship, namely: (Son, Daughter, Cousin, Niece, Mother, etc.).
4. A Query consisting of the Students Table joined to itself along with tblType would produce the desired results.

[RockKandee]

If I had a mother/daughter relationship... .
Would I make the following records?

A. Mom's student ID - B. Child's student ID - C. Mother

AND

A. Child's student ID - B. Mom's student ID - C. Daughter

[ADezii]

The way I see it, just the related Record (Daughter) for the Mother woould tell the story.