AnsweredAssumed Answered

How do I get the first (or last) record in a one to many relationship

Question asked by JuliaOlsen on Aug 2, 2016
Latest reply on Aug 2, 2016 by philmodjunk

The following is in context of an educational program for teachers.


I have a database where each entry in a "Teacher" table is joined to multiple records in a "Teaching History"  table by a "TEACHER_ID" match field.


The "Teaching History" table is joined to a table "All School Data" via "SCHOOL_ID.  This table contains relevant information about all the schools in the state.  Some teachers have changed schools since they began participating with us. I need to return the most current school each is teaching at.


I have a calculation field that returns the most recent year of employment "Current Year".


I run a script to find all currently active teachers and their current schools but it returns the oldest year.


How do I return the most recent year of employment for each teacher?


Side note: I can see the information correctly with a portal but that doesn't provide me with a complete set of exportable data (to excel, for example, for reporting purposes).


Thanks in advance.