I make a value list field in tblSchools called "Area"
Yes
...remove the area fields from tblEmployees
Yes.
and then later on make a junction table to bring the employees with the title of "Substitutes" on [tblemployees] together with the different Areas on [tblSchools]?
Not exactly. Schools don't figure into this junction table, just employees and areas. This is the format of that junction table:
EmployeeID, Area
17, 1
17, 3
17, 2
29, 2
As for your Area Supervisors, walk me through the data population method. How's this data getting loaded initially? My thinking is you already know who each school's Area Supervisors are, you're just going to load it into this finished structure. Or am I wrong--this data does not exist in a format that can be imported into your database and every piece of data is going to be rekeyed once you have it set up?
Also, moving forward, are Area Supervisors (or any other position ) going to move around a lot? My thinking is that a drop down isn't going to save you much.