Microsoft Office Tutorials and References
In Depth Information
Creating a Crosstab Query in Design View
2. Change the query to a Crosstab query by clicking the Crosstab button
in the Query Type group on the Design tab of the Ribbon.
Access displays a Crosstab row in the design grid (the grid in the
bottom half of the Design window). You use the Crosstab row to tell
Access how to build the Crosstab query. Access also displays the Total
row in the design grid, which allows you to choose one of the aggregate
functions or the Group By option.
In the next steps, you double-click fields in the Table pane of Design
view to move them to the design grid and then make choices from the
Crosstab row’s drop-down menu to specify the way each field is used to
create the crosstab.
3. Double-click the field you want to use for row labels in the Table pane
in the top half of Design view.
When you double-click the field name, Access moves it to the design grid.
4. Click the Crosstab row, click the down arrow, and choose Row
Heading from the drop-down menu.
5. Set the Total row to the Group By option for this column in the grid.
6. Double-click the field you want to use for column labels in the
Access places the field in the design grid.
7. Click the Crosstab row for the new field, click the down arrow, and
choose Column Heading from the drop-down menu.
8. Set the Total row to the Group By option for this column in the grid.
Click the Total row to display the arrow for the drop-down menu.
(Chances are that you won’t have to make this change.)
9. In the Table pane, double-click the field containing the values that
you want to aggregate in your Crosstab query to put that field in
This field — the Value field — provides the values that fill the Crosstab
10. Click the Crosstab row for the new field in the grid, click the down
arrow, and choose the Value option from the drop-down menu.
11. Choose the option to summarize the data from the drop-down menu in
the Total row of the Value field’s column.
Sum and average are common, but one of the other options may be the
one you need. See Chapter 2 of this minibook for more information on
Figure 4-8 shows Design view for a Crosstab query that creates a query
similar to the one created by the Crosstab Query Wizard in the