Summarizing Data with Crosstab Queries

Access offers another tool for summarizing data—the Crosstabquery. A Crosstab query uses a function to calculate data that is grouped by two types of information—one down the left side of the datasheet and another across the top. The specified calculation is performed at each row-column intersection resulting in a spreadsheet-like summary of the objects specified in the row and column headers. Crosstab queries allow the display of data in a format that facilitates analysis of grouped and calculated data.

Crosstab queries have at least three fields:

• Column heading field—identifies one parameter about the data that is to be studied. In the Car Sales database example that follows, it is car color. The query datasheet then contains one column for each color of car that has been sold. Only one column field is permitted in a Crosstab query.
• Row heading field—identifies another parameter about the data that is to be studied. In the Car Sales database example below, these headings are car make and model. The query datasheet contains one row listing each. Crosstab queries can have a maximum of three row heading fields.
• Summary value field—supplies data to be calculated at each intersection of a row and a column. In the following Car Sales database, this calculation is a count of cars sold to show which color of each model was selling the best. Any "0" values will display as blank cells.

The following Crosstab query example from a car sales database demonstrates these fields:

NOTE: The row sum field that you see in the previous diagram, which totals all calculations for the row, is optional.

Since Crosstab queries are a more advanced type of query, there is a wizard for creating them, however the Crosstab Query Wizard has limitations that make it less than ideal. The main limitation is that queries created using it need to be based on a single table or query. If a Crosstab query requires fields from multiple tables, a query that contains the needed information must first be created, and then that query can be used with the Crosstab Query Wizard.

In the next exercise, we will create a Crosstab query that pulls data from multiple tables. Rather than pull that data together into a query and use it as the data source for the wizard, we will create a Crosstab query in Design View.

Building a Crosstab Query in Design View

Let's create a Crosstab query in Design View that will show departments, courses and enrollments. The row headings will be DeptName and CourseTitle. The column heading will be SemesterYear. In the intersecting cells, a count of EnrollID will show students enrolled in each semester/year.

The query results will look like this:

Let's create this Crosstab query.

Step1. To begin creating a new query, on the Ribbon,

Click the Create tab, Click 

We see the query design window and the Add Tables pane.

Step2. Switch to the Tables tab in the Add Tables pane, if necessary.

Step 3. To add the tables we want to the query design window, in the Tables tab of the Add Tables pane,

Double-Click tblEnrollments, tblSections, tblCourses, tblDepartments

We see the selected tables in the query design window. Before we create the query itself, let's save and name the new query.

Step4. To save this query, press:

Control key + S, type: qxtbEnrollmentsByDepartmentsAndCourses Enter key

NOTE: The "qxtb" is the standard Access naming convention prefix for Crosstab queries.

Creating the Crosstab Query

By default, Access assumes new queries will be Select queries and the query design grid accommodates that query type. The first step in building a Crosstab query is to change the query type from the default Select Query to Crosstab Query.

Step1. To define the query type, in the Query Type group on the Ribbon,

Click 

Crosstab and Total rows have been added to the query design grid.

We want to first add to the grid the fields that will correspond to the rows in the spreadsheet. In this case, the fields are DeptName and CourseTitle.

Step2. To add the first row heading field to the query, in tblDepartments,

Double-Click DeptName

Step3. To add the second row heading field to the query, in tblCourses:

Double-Click CourseTitle

Two fields have been added to the query. Now we need to label them as row headings.

Step4. To position the cursor, in the DeptName column,

Click the Crosstab cell

We see a drop-down arrow.

Step5. To designate this field as a row heading,

Click , Click Row Heading

Step6. To designate the CourseTitle field as a row heading, in the CourseTitle column,

Click the Crosstab cell, Click , Click Row Heading

We see the designation of Row Heading for both the DeptName and CourseTitle fields.

Next we need to define the column heading field, which will specify the information to be displayed in the columns across the query result. Remember, only one field can be set as a column heading. In our example, we want to see the semester and year of the enrollments, and this information is contained the SemesterYear field.

Step7. To add the SemesterYear field to the query, in the tblSections field list,

Double-Click SemesterYear

The field is added to the query design grid. SemesterYear will be a column heading.

Step8. To designate this new field as a column heading, in the SemesterYear column,

Click the Crosstab cell, Click , Click Column Heading

The SemesterYear field is now a column heading.

Finally, we want to add the field that will calculate the data to display at the intersection of the rows and columns. Since every enrollment has a unique
EnrollID, we will count the values using that field.

Step9. To add the EnrollID field to the query design grid, in the tblEnrollments field list,

Double-Click EnrollID

We see the field added to the query.

Step10. To set this field to perform the Value calculation, in the EnrollID column,

Click the Crosstab cell, Click , Click Value

For the Value field, there is another step to determine the operation that should be performed on the values. We want to count the items, so we need to specify that calculation in the Total row.

Step11. To set the calculation to Count, in the EnrollID field,

Click the Total cell, Click , Click Count

The query design grid looks like this:

Adding a Total Column in Design View

We would like to have a total column in our Crosstab query. Let's see how to create a total column.

Since we are again counting number of enrollments in the total column, the EnrollID field is the field to use. Let's add the EnrollID field to the query design grid for a second time.

Step1. To add the EnrollID field to the query a second time, from the field list,

Double-Click EnrollID

The field is added with the Total cell and the Crosstab cell showing the default settings. We need to change these cells to create the Total field.

Step2. To set the calculation to be performed, in the second EnrollID field,

Click the Total cell, Click , Click Count

We must also choose the Crosstab setting for this column. We need to indicate that it is a row heading.

Step3. To define this field's purpose, in the second EnrollID field,

Click the Crosstab cell, Click , Click Row Heading

The query design grid looks like this:

The fourth column tells Access to count the number of EnrollID values per column, and the fifth column tells Access to count up all EnrollID values in a row to generate the row total. As we have seen in earlier examples, Access will give the automatic caption of CountOfEnrollID when we run the query. Let's give it a more readable caption using the same method we have used before.

Step4. To set the caption,

Click at the beginning of the second EnrollID field, type: Total Enrollments: Enter key

Step5. To see the query results, on the Ribbon,

Click 

Step6. Expand the column headings.

We see the query result listing departments and their courses, which are in alphabetical order:

7. To save the query, press:

Control key + S

Step8. Close the query.