Crosstab Queries : MS Access

Now look what happens when the same information is placed in a crosstab query, as shown in figure. Which do you think is easier to understand?

It's difficult to see the bottom line in a long list of records like this.

A query displays a summary view of figure's information.

In the Crosstab Query Wizard

A crosstab query in Design view.

You can create a crosstab query in Design View or by using the Crosstab Query Wizard. The Crosstab Query Wizard is usually much easier, but it does have some limitations:

If you need to use more than one table or query in the crosstab query, you will first need to create a separate query that has the tables you want to use.
You can't specify any limiting criteria when using the Crosstab Query Wizard. (But you can always modify the crosstab query in Design View and add the criteria yourself.)

In this lesson you will use the Crosstab Query Wizard to create a query that summarizes monthly ticket sales by tours.

In the Database window, click the Queries icon in the Objects bar and click the New button.

The New Query dialog box appears.
Select Crosstab Query Wizard and click OK.

The first step of the Crosstab Query Wizard appears. Here you need to select the table or query that contains the values you want. For this exercise you will use the ToursByName query as the source for the crosstab query.
Click the Queries option in the View section to display the queries in the database, select the qryToursByName query, and click Next.

The second step of the Crosstab Query Wizard is which field you want to use as the row headings for the crosstab. Let's use the TourName field for your row headings.
Double-click the TourName field and click Next.

The next step is determining which field you want to use for your column headings. Let's use the Date field as the column heading.
Double-click the Date field.

Because you selected a date field, the asks by which interval you want to group the dates: date, month, quarter, year, or date/time. For this exercise you want the date column to group dates by months.
Double-click the Month option.

Probably the most important step in the Crosstab Query Wizard is determining which field you want to calculate where columns and rows intersect and the type of calculation you want to use to summarize the fields.
Select the Number of Tickets field from the Fields list and the Sum option from the Functions list, as shown in figure.

This will calculate the total number of tickets sold for each tour, grouped by month.
Click Next.

You have to give your crosstab query a name.
Type qryTicketsByDate and click Finish.

Access saves the query with the name "qryTicketsByDate" and displays the results of the query, figure. Let's modify the crosstab query and add some limiting criteria.
Click the View button to display the crosstab query in Design view.

The crosstab query appears in Design view as shown in figure. Notice the Crosstab row, which you use to determine if a field should be a column heading, row heading, or value.
Double-click the First Class field in the qryToursByName list.

You only want to see tours for passengers without first class tickets.
Click the First Class column's Total row, click the list arrow and select Where from the list. Click the First Class column's Criteria row and type False.
Click the Run button on the toolbar.

Access displays the crosstab query, which only includes non-first class tickets.
Close the query without saving your changes.

To create a crosstab query:

in the database window, click the queries icon in the objects bar and click the new button.
select crosstab query wizard and click ok.
select the table or query you want to use and click next.
select the field you want to use as the row heading and click next.
select the field you want to use as the column heading and click next.
select the field you want to summarize, the type of calculation you want to use to summarize the field, and click next.
type a name for the crosstab query and click finish.