I have two simple tables; a schools table and a teachers table. There may be many teachers assigned to one school or no teachers assigned to a school. Each school in the school table has a unique ID number which is the primary key. Each teacher in the teacher table has a foreign key ID number to identify which school the teacher is assigned.
After several frustrating hours I have yet to construct a subsummary report layout that lists every school and assigned teachers. If I base the layout on the schools table I get all the schools, but only one teacher per school. If I base the layout on the teachers table I get all the teachers but no schools with no teachers.
How can I see all the schools and all the teachers in the same report?