How best to develop a report of summary statistics?
After reading about how self-joins can produce summary statistics (http://help.filemaker.com/app/answers/detail/a_id/3287/~/using-self-join-relationships-to-summarize-data-in-browse-mode), I'm stuck on whether they would be preferable to use in building the following report of summary statistics for an educational program for students experiencing academic difficulty.
The relevant (student) data are in two tables related by ID—academics and demographics. The former can contain more than one record per student; the latter contains only one (unique) record per student.
The data are organized in the fp7 by term and year (the field containing term and year is in the academics table);—fall 2010, winter 2011, spring 2011. As future terms are completed, students' data for those semesters will be imported into the fp7. So the report needs to be able to accommodate future data.
The summary report additionally needs to include the following statistics in a single layout.
# students eligible to participate in the program (demographics table; count of unique records)
# who participated (demographics table; count of unique records)
# who participated by college (demographics table; count of unique records)
# who experienced academic difficulty type 1 (academics table; count of multiple records per student)
# who experienced academic difficulty type 2 (academics table; count of multiple records per student)
# who experienced academic difficulty type 3 (academics table; count of multiple records per student)
If I use a self-join on either the academics or demographics table, one drawback is that the report's layout will display all of the records in the table. This probably will be an issue for the fp7's users.
However, if I instead a build a new table that contains only one record for the report's layout, then the report no longer will involve a self-join. Instead, the report presumably will involve a relation between the new table and the demographics and/or academics table(s). In that case, how do I generate the above values?
Many thanks in advance!