I was assuming a teacher could be associated with only one school. However, if you want to allow for historical records that permanently associate a school with booking regardless if teacher changes job to another school, then my suggested structure won't work. Instead, maybe:
tblBookings
ShowID
TeacherID
SchoolID
Bind that table to a form with 2 comboboxes. Set combobox LimitToList property as Yes and use combobox NotInList event to add records to the lookup tables.
http://www.blueclaw-db.com/access_no...ed_example.htm
Use form/subform arrangement.
http://office.microsoft.com/en-us/ac...010098674.aspx
1. main form bound to tbShows and subform bound to tblBookings with comboboxes for teachers and schools
or
2. main form bound to tblSchools and subform bound to tblBookings with comboboxes for teachers and shows
or
3. main form bound to tblTeachers and subform bound to tblBookings with comboboxes for shows and schools