MS Access Table with Multiple foreign keys to same table

I have a table in MS Access that has 4 columns in it and they all contain an id that is the primary key in another table and I want to know if there is another way to do this and if not how can I get Access to let me join on these without the error that it can't tell what I am trying to join on. Pretty much these id's are part of a student table and they contain ids for their supervisor for that block and there are 4 blocks and I need to be able to track what students each supervisor had for each block.

Supervisor Table:

CREATE TABLE supervisors 
( id NUMBER primary key
, last_name TEXT
, first_name TEXT
, mi TEXT
, m_number TEXT
, email HYPERLINK
, status TEXT
, note开发者_运维知识库s MEMO
) ;

Student Table:

CREATE TABLE students 
( id NUMBER primary key
, last_name TEXT
, first_name TEXT
, mi TEXT
, m_number TEXT
, email HYPERLINK
, blk1_supervisor_id NUMBER
, blk2_supervisor_id NUMBER
, blk3_supervisor_id NUMBER
, blk4_supervisor_id NUMBER
) ;

I can't get it to let me make the blk1, 2, 3, 4 fields foreign keys to the supervisor table let alone do a join on say blk1_supervisor_id and blk2_supervisor_id at the same time.

Without knowing your table schema, this is the best I can do for you...

SELECT SV.Name, S.Name, SB.Block
FROM Students as S

   INNER JOIN Student_Blocks AS SB
   ON S.StudentID = SB.StudentID

   INNER JOIN Supervisors AS SV
   ON SB.Block1SupervisorID = SV.SupervisorID
   AND SB.Block2SupervisorID = SV.SupervisorID
   AND SB.Block3SupervisorID = SV.SupervisorID
   AND SB.Block4SupervisorID = SV.SupervisorID

ORDER BY SB.Block

You can keep your Supervisors table as it is now, have a Students table that has only true Student properties (down to email). then you create a StudentsSupervisor relationship table:

CREATE TABLE StudentsSupervisors 
( StudentId NUMBER 
  SupervisorId Number )

where both fields constitute the PK. If you NEED to identify further the relationship, you coudl add the blockNumber to that table, and eventually include it in the PK if the same SuperVisor can be linked to the same Student twice in different blocks.

A quick and easy way to normalise your tables correctly. Choose one option from each statement below. A student can have none/one/many supervisors. A supervisor can have none/one/many students. In your case, a student can have many supervisors and a supervisor can have many students. The relationship between students and supervisors is thus many-many, which is not allowed in a RDBMS. In such circumstances you must create a join table between the two, as Patrick Honorez has shown you.