Trouble with relationships
I'm trying to create another databse for my classroom, this one to track performance on math standards. Each standard is tested at least four times, and each of those times, I want to record certain information (student, date, type of question, etc.). I want to generate reports per standard (like the average % correct on that standard for the entire class), but I also want to generate reports per child (like a list of all standards and this child's average on each of those standards). The latter part is where I'm having trouble.
I can generate reports per standard beautifully. And I can even generate reports per child 1 standard at a time. But I cannot generate a report per child that lists ALL the standards.
This is how my database is set up:
I have 1 table per test (since each standard is getting at least 4 tests), and each of these tables has at least 5 fields: student, date, type of question, etc.
Each of those tables is linked to a Standards table that lists information about the standard as well as averages per class for that standard.
That table is linked to a StudentReports that generates reports per child 1 standard at a time (through a portal that displays data from Standards). This more or less works as expected.
And that table is linked to a Students table.
I just want to be able to list ALL of the standards for a given child and the corresponding averages. I tried creating an "intermediary" table (Trial) that links both Standards and Students, but that doesn't seem to make a difference. I'm pretty sure the problem is with relationships, but I'm not sure how to fix it. I'm attaching a screenshot of the relationships from FM.
What am I missing here? Any help would be greatly appreciated. Thank you!