- Create a crosstab query.
- In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries under Objects, and then click New on the Database window toolbar.
- In the New Query dialog box, click Crosstab Query Wizard, and then click OK.
- Follow the directions in the wizard dialog boxes.
If the wizard doesn't start
This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.
For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.
For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.
In the last dialog box, you can choose to run the query or see the query's structure in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
Note If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design view.
- Open the query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
- Display the query's property sheet (property sheet: A pane that is used to view or modify the properties of various objects such as tables, queries, fields, forms, reports, data access pages, and controls.) by double-clicking anywhere outside the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) and outside the field lists.
- In the ColumnHeadings property box, type the column headings you want to appear in the report, separating each one with a semicolon. If a column heading contains spaces, enclose it in double (") quotation marks.
For instance, in a query showing sales by quarter, you would type:
"Qtr 1";"Qtr 2";"Qtr 3";"Qtr 4"
- Create a blank report that is bound to the crosstab query.
- Add the controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) for the fields you want to appear in the report.
- Place labels for the row headings and column headings in the page header (page header: Used to display a title, column headings, dates, or page numbers at the top of every page in a form or report. In a form, the page header appears only when you print the form.).
- Place text boxes for the row headings and column values in the detail section (detail section: Used to contain the main body of a form or report. This section usually contains controls bound to the fields in the record source but can also contain unbound controls, such as labels that identify a field's contents.).