Relationship from the same table. I'm using student database template.

[mrsjtamuno]

I am new and self taught with access.



I work for a school district and we are using the student database template to keep track of students involved in gangs. I need to be able to open one student record and see a list of associates which just link to other student records already in the database. I have no clue where and how to do this. The research I have found said something about a "self join" but I'm not sure if I'm on the right track.

I have a tab in Student Details for Associates but it's empty. However what I would like is to be able to use that tab to see the list of other student records that they associate with. There is a lot of overlap and I dont want to have to create numerous records of the same student.

[aytee111]

You need a table that has two fields - studentID_1 and studentID_2. This will allow you to display all associates: when you select a student look in both fields for that studentID and display all the students that are related. You can do this in two queries - looking first in studentID_1 and second in studentID_2, joined to the students table to get their names, and then do a UNION query to get the entire list.

[ranman256]

you need a tAssociates table
[studentID], [AssocID]

(key both fields so you dont get duplicates)

make a subform in the main studend form for this table, link them on [studentID] field.
now you can add associates.
to see the names of the students , in the subform instead of text box for [AssocID], put a combo box (still linked to [AssocID])
the combo will show [StudentID], StudentNAME
set the combo properties:
columnWidths: 0;2

this will show the name and the ID will be hidden.

[orange]

Can a Student be in more than 1 gang?
Your application/database seems much more extensive than student and associate(s).

[mrsjtamuno]

They can be in any gang, which is listed separately as just a short text fill in on their record. The associates can be any of the other student records regardless of their gang affiliation.

Can a Student be in more than 1 gang?
Your application/database seems much more extensive than student and associate(s).

[mrsjtamuno]

Im sorry but I'm still very confused about where and how to do this. I know how to add fields to the student table but after that im lost.

You need a table that has two fields - studentID_1 and studentID_2. This will allow you to display all associates: when you select a student look in both fields for that studentID and display all the students that are related. You can do this in two queries - looking first in studentID_1 and second in studentID_2, joined to the students table to get their names, and then do a UNION query to get the entire list.

[mrsjtamuno]

I tried to use this video to help me but it only allows me to add one person as an associate. I need to be able to add multiple students as associates and appear in list form.
I wish it was as easy as just putting a hyperlink to the associated student records. I'm very lost on how to start to do this.

you need a tAssociates table
[studentID], [AssocID]

(key both fields so you dont get duplicates)

make a subform in the main studend form for this table, link them on [studentID] field.
now you can add associates.
to see the names of the students , in the subform instead of text box for [AssocID], put a combo box (still linked to [AssocID])
the combo will show [StudentID], StudentNAME
set the combo properties:
columnWidths: 0;2

this will show the name and the ID will be hidden.

[orange]

Here is a link with a lot of info re database planning and design. The tutorials from RogerAccessLibrary will show you a procedure that can be used with any database--but you have to work through the tutorial.
Spend some time learning concepts and design. Don't be too quick to jump into physical Access database.

Good luck.

[mrsjtamuno]

Thank you Thank you Thank you!!!!

Here is a link with a lot of info re database planning and design. The tutorials from RogerAccessLibrary will show you a procedure that can be used with any database--but you have to work through the tutorial.
Spend some time learning concepts and design. Don't be too quick to jump into physical Access database.

Good luck.

[orange]

You are welcome.
Good luck with your project.