Establishing a 1:m relationship using a query and a table

[Guest]

I am still working on my training records database. I have got my three main
tables sorted out (hoorah! LoL).

To get a list of supervisors, it was suggested to me that I put a checkbox
in the employee table and then create a query. At the moment I have a list of
all supervisors but, as is always the way, the db could get a bit more
complicated because not all supervisors would teach all courses.

Putting it another way, when I enter a course record for an employee, I want
the list of valid courses to be selected dependent on their subdepartment and
the course type. I want the valid list of supervisors to be dependent on the
course taken.

1) Each department has many subdepartments
2) Each subdepartment can have many courses
3) Each course can have many subdepartments
4) Each supervisor can teach many courses
5) Each course can have many supervisors

So from top to bottom:

lkup_dept
Dept (PK)

lkup_subdept
dept (FK)
subdept (PK)

lkup_crselink
subdept (FK)
courseID (FK)

lkup_course
courseID (PK)

lkup_SpvsrLink
courseID (FK)
EmployeeID (FK)

Supervisor
EmployeeID (PK ??)
Name
Supervisor

So the question is: How can I establish a 1:m relationship using a query and
a table.

Or would it be easier to connect "tble_employee" with "lkup_SpvsrLink"
directly?

 

[Guest]

I have connected the two tables together and applied a filter so it only
includes employees who are also supervisors.

Is there any way to prevent a form from being unfiltered ??

Is there a better solution to my original query...?

 

[Guest]

Ignore this post. I think I am ok.