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.
Thanks -cdwd
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.
Thanks -cdwd
