Question Table field looking at its own table

[music_al]

I have a table [tbl_Students] which holds Student Details. In the table I want to add a field which lists any siblings a student may have in the same school.

To do this, the siblings field has to lookup the table that its in, but I dont think Access will do this. I tried to create a Junction Table Jntbl_Siblings and add 2 foreign keys from the same table. This doesnt seem to work either. Can anyone help ?

Thanks


Al

 

[Banana]

Self-join is definitely possible, but there are steps you need to take, namely aliasing the table.

Look at Allen's excellent example of self-joins.

 

[music_al]

Thanks for this, but... (there's always a but)

in my siblings field, I want to be able to link MANY records from the table, i.e. the current student may have 2 brothers and sister who are listed in the table.

The example you sent only seems to allow ONE record to be linked.

Any ideas ?


Al

 

[Banana]

Hmm. How is your table structured? It may help us provide more specific suggestions.

To my mind, if I had to know who were sibling then I would want to track the family as well, and thus assign a FamilyID to all studentID, so all sibling would have same FamilyID. This is even if I didn't particularly care about whom parent are or whether they have uncle & grandparents in the house; it's just as useful as "grouping identifier". A new student enrollment would auto-create a new FamilyID if they weren't sibling of any existing sibling.

Is that close?

 

[gemma-the-husky]

i think you could do this via a chain of pointers - so each sibling links to the next sibling down, until the youngest has no sibling.

alternatively, and probably better, you could have a second sibling table to group together the details of related siblings.

complications are entirely possible in a school situation - eg how would you deal in step siblings/half-siblings. where child a is half sibling to child b, and child c is half sibling to child b, but child a is not related to child c. Or what about siblings that dont live together.

 

[RossWindows]

If I remember correctly, that information is probably obtained from each student's registration form.
So, if student a says they are related to student b, the school assumes this to be true.
But, what if student b does not say they are related to student a?
I think by building a junction table you could handle this either way.

 

[music_al]

OK - heres how I fixed it.

I have a field for Mother and a field for Father in the Student table.

I created a query that uses the Student_ID, Father_ID and Mother_ID
I have set the following criteria...

Father_ID = [Forms]![frm_Students]![Father_ID]
Or
Mother_ID = [Forms]![frm_Students]![Mother_ID]

I have also set up a criteria in the Student_ID...

<> Student_ID = [Forms]![frm_Students]![Student_ID]

...so it doesnt return itself in the query.

This means that the query returns any other students where they have the same mother OR the same father, so it includes Step Siblings as you pointed out Gemma - and this is what I wanted.

I then just drag and dropped the Query into my main form and it works fine. I would prefer it the siblings were in a ListBox and could be clicked to jump to that Student record, but i will work on that another time.

Thank you all for your help

Al

 

[Banana]

FWIW, a listbox can be unbound and have a rowsource referencing the same query as you are using for subform. To prevent selection, I *think* you can set Enabled to No (or maybe it's actually Locked. I can't remember which will disallow selection but allow scrolling. One disables both while other merely disable one but not other)

 

[music_al]

I had a play around with the list box idea and I got it to work

 

[Atomic Shrimp]

OK - heres how I fixed it.

I have a field for Mother and a field for Father in the Student table.

...

This means that the query returns any other students where they have the same mother OR the same father, so it includes Step Siblings...

But not orphans. Maybe that's never going to be a problem in reality...

 

[Banana]

FWIW, I'd not want MotherID and FatherID but rather a GuardianID, related to tblGuardians tbl with a column, "GuardianTypeID" to identify mother, father, guardian, adult sibling, ward of state, whatever.

It also sounds to me Guardian - Students is a many-many relationship.

 

[music_al]

I did think about using a Guardian table for Guardian, Mother and Father, but, it may well be that the father of one child, might be the Guardian of a different child. I think forcing a Guardian type against a Guardian record might be restrictive.

It seems to work fine as I have it right now.

Al

 

[RossWindows]

You can use the guardian table with a junction table to display a many to many relationship. Also, you could add fields to the junction table to describe each relationship.

The junction table might look something like this:

GuardianID, Relation, StudentID
1, "Is Mother of", 1500
2, "Is Father of", 1500
2, "Is Guardian of", 1580

 

[music_al]

Thanks Ross, I will give that a go...