AnsweredAssumed Answered

Value List and Associated Field

Question asked by JuliaOlsen on Nov 8, 2013
Latest reply on Nov 27, 2013 by JuliaOlsen


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.