First, you would make your own life much easier if you added records for Beatrice and Cathy to TABLE#1 and left the unknown information in those records blank. That way you don't have to worry about what the missing records will do to your queries.
Second, I'm assuming from your statements that it is the teacher name in TEACHER_1 and TEACHER_2, rather than the teacher key, which would have been the proper way to design the database.
Try this -
Code:
SELECT
TEACHERS.NAME,
TEACHERS.EMAIL,
TEACHERS.PHONE,
P1.COURSE,
P1.CURRICULUM,
P1.SCHOOL,
P1.TEACHER_1 AS T1_Name,
NZ(T1.EMAIL,"") AS T1_Email,
NZ(T1.PHONE,"") AS T1_Phone,
P1.TEACHER_2 AS T2_Name,
NZ(T2.EMAIL,"") AS T2_Email,
NZ(T2.PHONE,"") AS T2_Phone
FROM
((PLACEMENTS AS P1 LEFT JOIN TEACHERS AS T1
ON P1.TEACHER_1=T1.NAME)
LEFT JOIN JOIN TEACHERS AS T2
ON P1.TEACHER_2=T2.NAME);
If it complains about the join, try this:
Code:
FROM
(TEACHERS AS T2 RIGHT JOIN
(PLACEMENTS AS P1 LEFT JOIN TEACHERS AS T1
ON P1.TEACHER_1=T1.NAME)
ON P1.TEACHER_2=T2.NAME);