Value List and Associated Field
I have a database connecting (among other things) teachers with their districts and schools.
I need to track the teachers each year they are in our program and beyond, so I have a table called "Education Employment History"
Education Employment History Needs to have a record for each teacher for each year with the fields year, district, school
I have a table called "All School Data" which lists district, school, address, city, zip, phone, principal, email). There is a record number called "SchoolID" which is distinct for each record (of course).
These two tables are related by "District" and I have created a value list where, in my form, in the "district" field, I can choose the appropriate district from a drop down. I then can choose from only the schools in that district to populate the "school" field.
That part works well.
The problem is, I want to display the school address, phone number associated with a given school but I am consistently getting only the first record from the schools in the district. So I might select the school from record 3, but I’m getting the address, etc from School record 1.
District -------> School Record 1: school, address, principal, etc.
School Record 2: school, address, principal, etc.
School Record 3: school, address, principal, etc.
Any suggestions are appreciated. Have uploaded a graphic of the two tables relationship. They are connected by district. I did try using the school ID but that did now allow me to do the value list as I needed.