O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Creating a Crosstab Query to View Complex Information

Problem

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?

Solution

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.

A table with instructors, courses, and grades

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;

Running the query in Figure 2-25 produces a result with 25 records, shown in Figure 2-26.

Now for the alternative. A crosstab query will return the same student counts, per instructor, per course; however, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required