Creating a spreadsheet-style subform from three tables
Posted on 2008-10-27
I have a database for educational applications, and want to be able to see all scores for every student in a particular class section in a spreadsheet-like layout.
Among other tables, there is a ClassRoster table with the students in a particular section, and an Assignments table with the assignments in the particular class section. Both of these are one-to-many to the Scores table.
ClassRoster 1->M Scores M<-1 Assignments
(The Assignments table is also many-to-one to the ClassSections table, and this chart would be a subform of a form based on ClassSections so the user would see only the assigments for a particular class section, but I don't think that matters.)
The chart I want to create would have assignments as columns, roster entries (that is to say, students) as rows, and items from the Scores table at the intersection of the two.
What would I use for this? A crosstab query wouldn't work, I think -- there's no summary function for the intersections; the data comes from a different table. Would I use a PivotTable? And how, specifically, would I construct that as far as the query data source and such go?