How can I view my relational data in a hierarchical manner? I know I can use Group By clauses in a query, but with an abundance of data points, this becomes cumber-some and creates many records. Is there another way to see summaries of data based on groupings?
A crosstab query is a great alternative to a standard select that groups on a number of fields. Figure 2-24 shows the Student_Grades table, which has five fields: StudentID, Instructor, MidTerm Grade, Final Grade, and Course.
Figure 2-24. A table with instructors, courses, and grades
Using the information in the Student_Grades table, it is possible to get a per-instructor count of how many students attended each course. Figure 2-25 shows the design of a standard select query that accomplishes this. The
Group By clauses create delineations of Instructor and Course, and within each combination, a
Count of the StudentID field returns the count of students. The SQL for this query is:
SELECT Student_Grades.Instructor, Student_Grades.Course, Count(Student_Grades.StudentID) AS CountOfStudentID FROM Student_Grades GROUP BY Student_Grades.Instructor, Student_Grades.Course;
Now for the alternative. A crosstab query will return the same student counts, per instructor, per course; however, ...