Tricky relationship question 1

[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.

Thanks -cdwd

 

[jebry]

Hi cdwd!

Putting the staff id in the instructor table is the best way, but don't create any relationship between the tables (or, at the very least, uncheck the enforce referential integrity). You can create the relationship in queries you build to use both tables and you can select the proper join to allow all the entries from the instructor table and matching entries from the staff table or visa versa depending on your needs.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[vistor]

Thanks,

I will delete the relationship I have between the staff table and the instructor table. This is the SQL I have on the Instructor form:

SELECT Instructor.Ins_ID, Instructor.Staff_ID, Instructor.Prefix_ID, Instructor.Ins_First_Name, Instructor.Ins_Nickname, Instructor.Ins_Middle_Name, Instructor.Ins_Last_Name, Instructor.Ins_Email, Instructor.Ins_Phone, Instructor.Ins_Phone_Ext, Instructor.Ins_Fax, Instructor.Ins_Addr1, Instructor.Ins_Addr2, Instructor.Ins_City, Instructor.Ins_State, Instructor.Ins_Zip, Instructor.Ins_Company, Staff.Staff_First_Name, Staff.Staff_Nickname, Staff.Staff_Middle_Name, Staff.Staff_Last_Name, Staff.Staff_TitlePosition, Staff.Staff_Email, Staff.Staff_Phone
FROM Instructor INNER JOIN Staff ON Instructor.Staff_ID = Staff.Staff_ID;

I have the staff_id as a combo box, with it's source as the staff table but updating to the staff_id on the instructor table.

I will see if I can get everything to work now with your suggestions.

Thanks

 

[vistor]

I'm still having the problem. If the instructor is from an outside organization and I fill in the instructors fields but not any of the staff fields - the staff id automatically fills in with a zero and I get the error message that the microsoft jet can't find a matching number in the staff table.

 

[jebry]

Hi!

Instead of an Inner Join, you need to do a Left Join.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[vistor]

Zippity-Do-Da, zippity-Yeah! You're the coolest! Thanks!!!!

 

[jebry]

I take it it's working!

Jeff Bridgham
bridgham@purdue.edu