How to Create an Access Crosstab Query with Two Column Headers
Question details
The user needs to count machine sales categorized by customer, model, and month, and wants to display the results in an Access crosstab query using two dynamic column headers (model and month).

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Generating a complex sales report within a database where data must be grouped across two different column dimensions simultaneously.
- Observed behavior
- Microsoft Access crosstab queries natively support up to three row headers but strictly limit users to a single column header, preventing the direct assignment of both 'Month' and 'Model' to the column axis.
Verify that your MachineSales table contains standardized and clean data for the customer, model, and sales-date fields before attempting to manipulate the query structures.
Concatenate Fields into a Single Column Heading
Since Access restricts crosstab queries to a single column heading, combining the two desired fields (month and model) into one concatenated field is the most reliable native workaround.
By merging the data of two fields into one string, Access reads it as a single data point, allowing it to bypass the one-column-header limitation while still displaying the required information dynamically.
Open your database in Microsoft Access, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.
Add your 'MachineSales' table to the grid. In the Query Design ribbon, click 'Crosstab' to change the query type. Add your Customer field and set its Crosstab row to 'Row Heading'. Add your counting field and set it to 'Value'.
In a blank column on the design grid, type a concatenation expression combining the date and model. For example: HeaderName: Format([SalesDate],"yyyymm") & " - " & [Model].
In the 'Crosstab' row for this newly created expression field, select 'Column Heading'. Run the query to see your combined dynamic headers.

Export Data to a Spreadsheet PivotTable
If you strictly require a layered, two-tier visual structure for your column headers, exporting the data to a spreadsheet application to create a PivotTable is the most effective approach.
Define Fixed Column Headings in Properties
If your combinations of months and models are static and you want to enforce a specific display order, you can lock them in using the query's property sheet.
Analyze Complex Data Easily with WPS Office
While Microsoft Access handles databases well, generating multi-layered crosstab reports natively can be rigid. For advanced data visualization and true dynamic dual-column headers, PivotTables are vastly superior. WPS Office provides a free, highly compatible alternative to Microsoft Office. With WPS Spreadsheet, you can easily import your database records and construct complex, multi-tiered PivotTables without writing complicated query expressions.
- 1. Download and Install: Download WPS Office Free from the official website and install the suite on your computer.
- 2. Export Database Data: Export your Access query data to a standard Excel (.xlsx) file format.
- 3. Analyze with PivotTables: Open the exported file in WPS Spreadsheet, highlight your data, and use the robust PivotTable feature to drag and drop multiple fields into your column headers.

Frequently Asked Questions
Can the Access crosstab query wizard create multiple column headers automatically?
No, the built-in Access crosstab query wizard supports up to three row headers, but it restricts you to exactly one column header. You must concatenate fields or use a spreadsheet PivotTable to bypass this.
How do I format dates properly when concatenating fields for a column header?
You can use the Format() function within your query expression. For example, typing Format([SalesDate],"mmm yy") & " " & [Model] will format the output to display headers like 'Jan 21 WA470'.
Why are my Access crosstab query column headers sorting alphabetically instead of by date?
When dates are converted to text during concatenation, Access sorts them alphabetically by default. To resolve this, format the date numerically as year-month (e.g., Format([SalesDate],"yyyy-mm")) or explicitly define the exact display order in the query's 'Column Headings' property.
Is it possible to automate the export of Access data to a spreadsheet PivotTable?
Yes, you can use VBA (Visual Basic for Applications) macros within Access to automate running the select query, exporting the results to a spreadsheet format, and triggering a refresh on an existing PivotTable.




