Summarizing Values with a Crosstab Query

A crosstab query allows you to summarize the contents of fields that contain numeric values, such as Date fields or Number fields. In this type of query, the results of the summary calculations are shown at the intersection of rows and columns. Crosstab queries can also involve other functions such as the average, sum, maximum, minimum, and count. You cannot update crosstab queries. The value in a crosstab query cannot be changed in order to change the source data.

Create a Crosstab Query

In the Database window, click Queries on the Objects bar, click New, click Crosstab Query Wizard, and then click OK.

From the list at the top of the dialog box, select the table or query that contains the records you want to retrieve.

Click Next to continue.

Double-click the field(s) you want to use in the crosstab query.

Click Next to continue.

Select the field for the columns in the crosstab query.

Click Next to continue.

Click the field whose values you want to be calculated and displayed for each row and column intersection.

Click the function you want for the calculation to be performed.

Select the Yes, Include Row Sums check box if you want to see a total for each row, or clear the check box if you do not want to see a total for each row.

Click Next to continue.

Enter a name for your query.

Indicate whether you want to immediately view the query or modify the design.

Click Finish.

Did You Know? You can use a PivotTable instead of a crosstab query. Display crosstab data without creating a separate query in your database either by using the PivotTable Wizard in a form, or by creating a PivotTable list in a data access page. You can change column headings in a crosstab query. If you want to change the column headings, open the query in Design view, and then open the Properties dialog box for the query. Enter the column headings you want to display in the Column Headings property box, separated by commas.