5 Replies Latest reply on Jan 6, 2014 11:41 AM by philmodjunk

    Database structure problem



      Database structure problem



           I am building a system that allows classroom teachers to record observations of children. An observation is of one or more children doing an activity that represents one or more of the 18 aspects of the curriculum. When the observation is complete a script populates an intermediate table with records which link one child with one aspect, so that if the observation is of three children doing an activity that involves both maths and writing we will have six records.

           I now want a layout that shows for each child the 18 aspects of the curriculum and how many observations have been made of that child in each aspect. It's easy to show this for those aspects that have been covered, using a summary field and a subsummary part on the layout. But I want a tabulation of all 18 with a zero next to those that have not yet been observed (this is the most important piece of information as far as the teacher is concerned).

           Of course, a teacher can surmise the zero from the absence of an aspect from the list, but this would make side-by-side comparisons of reports difficult and in any case it is something that a database ought to be able to do, surely.

           Thanks in advance.

        • 1. Re: Database structure problem

               For this report, Do you need to see this information for all students in a single report or would it work to set it up for one student at a time?

          • 2. Re: Database structure problem

                 In my experience teachers are still addicted to paper, so if we were able to make one and print it, make the next and print it, and so on I think that would serve.

                 Thanks for your interest.

            • 3. Re: Database structure problem

                   Does that mean that a report that limits this data to that for a single student at a time is acceptable?

              • 4. Re: Database structure problem

                     Yes, that would be fine.

                • 5. Re: Database structure problem

                       Best guess is that you have these relationships though the names that I am using are likely different to some degree:


                       Students::__pkStudentID = Observations::_fkStudentID
                       Aspects::__pkAspectID = Observations::_fkAspectID

                       If we add a global field, gSelectedStudentID to Aspects, we can then click over to Relationships and use the duplicate button (two green plus signs) to make a duplicate occurrence of Observations and link it to Aspects like this:

                       Aspects::__pkAspectID = Observations|SelectedStudent::_fkAspectID AND
                       Aspects::gSelectedStudentID = Observations|SelectedStudent::_fkStudentID

                       You can then define a calculation field in Aspects with this expression: Count ( Observations|SelectedStudent::_fkStudentID ) to count the number of Observations linked to that Aspect for the selected student. You can clear the "do not evaluate if all referenced fields are empty" check box to get a zero when there are now such observations for a given Aspect record.

                       Now you can use a script to assighn the value of __pkStudentID to gSelectedStudentID and then change layouts to a list view layout that lists all the records in Aspects with the calculation field with the count function to show the total number of observations for each Aspect for the selected student.

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained