Microsoft Office Tutorials and References
In Depth Information
The quickest way to create a crosstab query is to use the Crosstab Query Wizard ,
which guides you through the steps for creating one. You could also change a select
query to a crosstab query in Design view using the Crosstab button in the Query Type
group on the Query Tools Design tab.
Microsoft Office Access Help
provides more information
on creating a crosstab query
without using a wizard.
Reference Window | Using the Crosstab Query Wizard
• In the Other group on the Create tab, click the Query Wizard button.
• In the New Query dialog box, click Crosstab Query Wizard, and then click the OK button.
• Complete the Wizard dialog boxes to select the table or query on which to base the crosstab
query, select the row heading field (or fields), select the column heading field, select the cal-
culation field and its aggregate function, and enter a name for the crosstab query.
The crosstab query you will create, which is similar to the one shown in Figure 5-20,
has the following characteristics:
• The qryCustomersAndInvoices query in the Panorama database is the basis for the new
crosstab query. The base query includes the CustomerID, Company, FirstName, LastName,
City, InvoiceAmt, and InvoicePaid fields.
• The City field is the leftmost column in the crosstab query and identifies each crosstab
• The values from the InvoicePaid field, which is a Yes/No field, identify the rightmost
columns of the crosstab query.
• The crosstab query applies the Sum aggregate function to the InvoiceAmt field values and
displays the resulting total values in the Paid and Unpaid columns of the query results.
• The grand total of the InvoiceAmt field values appears for each row in a column with
the heading Total Of InvoiceAmt.
You are now ready to create the crosstab query for Oren.
To start the Crosstab Query Wizard:
1. If you took a break after the previous session, make sure that the Panorama data-
base is open and the Navigation Pane is closed.
Trouble? If the Security Warning is displayed below the Ribbon, click the Options
button next to the Security Warning. In the dialog box that opens, click the
“Enable this content” option button, and then click the OK button.
2. Click the Create tab on the Ribbon and then, in the Other group on the Create tab,
click the Query Wizard button. The New Query dialog box opens.
3. Click Crosstab Query Wizard , and then click the OK button. The first Crosstab
Query Wizard dialog box opens.
You’ll now use the Crosstab Query Wizard to create the crosstab query for Oren.
To finish the Crosstab Query Wizard:
1. Click the Queries option button in the View section to display the list of queries in
the Panorama database, and then click Query: qryCustomersAndInvoices .See