Many to Many self-join

[Hurlz]

I have wrestled with this, but am failing.

I have Students and Supervisors. Supervisors can have more than 1 student, and students will definitely have more than one supervisor. I would like to look at a student record and see their supervisors, and look at a Supervisors record to see all their students. I am assuming this should be in a portal, but I am having trouble with the self joining aspect.

I want to keep them in the one contacts list, as there are other tables (eg Events) that both students and supervisors and other contacts will relate to. That stuff all works well. I think I need a many to many self-join, but I seem to be stuffing it up.

I have created a simple sample database with what I have done so far attached. Please feel free to advise me or edit.  Thank you.

Self join advisors students.fmp12

[Wim Decorte]

something like this will do:

[Hurlz]

Hi Wim, Great. Thank you for that. That worked.

Cheers

[Greg58]

Can I jump in here and ask which keys are the primary keys for the advisors and students in Wim's solution please?

[Wim Decorte]

Since there is no Advisor table nor a Student table, there are no primary keys for those entities.

The design choice here is to go with a people table called Everyone and the primary key for that table is the field "PrimaryKey"

The advisor or student role is just an attribute of the Everyone entity, expressed by setting the Advisor ID/Student ID in Everyone and creating a record in the JOIN table to link up someone as advisor with someone as a student.

This is just one of a few ways you can design this.

[Greg58]

Great.  Thanks for the explanation 😀

[Greg58]

Just wondering if you can explain how the portals are set up please?  Can't get them to work.  I have attached a pic of my setup.  Thanks.