Table / Forms Relationship Problem

[vistor]

Hi,

I have a staff table - staff_id, staff_name, etc.

instructor table - instructor_id, instructor_name, etc.

course table - course_id, title, etc.

Staff may take courses - I have a staff_course table, fields staff_id and course_id.


Staff Form has the main info from staff table with a datashet subform with the courses the staff take. Basic set-up.

Instructors may teach course - I have a instructor_course table, fields instructor_id and course_id.

Instructor Form has the main info from instructor table with a datashet subform with the courses the instructor teaches. Basic set-up.

My problem is that Instructors may be staff members as well as from an outside organization. Staff table and Instructor table by have different fields.

How do I create the staff / instructor table relationship and have a Instructor form that has fields for the staff and separate fields for an outside instructor? I need to have a lookup combo box for the staff on the instructor form, for when an instructor is a staff member.

I've tried having the staff_id on the instructor table. But this creates problems when the instructor is not a staff member. When the user creates a new instructor record that is not a staff member, a warning comes up saying that a matching staff_id can't be found in the staff table.

Thands -cdwd

 

[yotwiggy]

Instead of having two tables for Staff and Instructors, how about making them one table since Instructors are staff even if they are out side, they do the work. You could have

staff_id
staff_name
staff_function

staff function table could be
S - staff
SI - Staff/Instructor
OI - outside Instructor

your form could look up by id and function, since all three could possible take courses.

 

[vistor]

Thanks for the idea.