5 Replies Latest reply on Dec 12, 2013 11:22 AM by michael.teng.0

    Tie a specific test record to the student's corresponding enrollment record

    Karen Payne

      Hello!

       

      We are creating a new view displaying our state assessment data and we are in need of help with narrowing our data.  We are including the enrollment table to select the student's school and grade.  The issue is we need to tie a specific test record to the student's corresponding enrollment record. 

       

      So far we have created a custom SQL data connection using the following logic at the end of the connection:

      where [Enrollment].[startDate] <= [TestScore].[date] and [TestScore].[date] <= [Enrollment].[EndDate] or

      [Enrollment].[startDate] <= [TestScore].[date] and  ([Enrollment].[EndDate]) is null

       

      This works for the majority of our students, however, our test date is just a generic date.  For example all student test records are imported with a date of May 1st.  We may have a student that enrolled on May 2nd and took the test.  This student would be excluded from our data connection.

       

      Does anybody have thoughts on a better way of selecting the data?

       

      Thank you!

      Karen Payne