[ Team LiB ] Previous Section Next Section

Summarizing Values with a Crosstab Query

graphics/microsoft154_1.gif

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

graphics/1_icon.jpg

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

graphics/2_icon.jpg

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

graphics/3_icon.jpg

Click Next to continue.

graphics/4_icon.jpg

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

graphics/5_icon.jpg

Click Next to continue.

graphics/6_icon.jpg

Select the field for the columns in the crosstab query.

graphics/7_icon.jpg

Click Next to continue.

graphics/06inf22.jpg

graphics/8_icon.jpg

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

graphics/9_icon.jpg

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

graphics/10_icon.jpg

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.

graphics/11_icon.jpg

Click Next to continue.

graphics/12_icon.jpg

Enter a name for your query.

graphics/13_icon.jpg

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

graphics/14_icon.jpg

Click Finish.

graphics/06inf23.jpg

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.


    [ Team LiB ] Previous Section Next Section