Crosstab Queries in Access: Turn Rows Into Columns
Crosstab queries transform your data from rows into a pivot-table-style grid. Learn how to build them in Access and when to use them effectively.
If you have ever used a pivot table in Excel, you already understand the concept behind a crosstab query. A crosstab query takes data that is stored in rows and presents it in a grid format — with one field as row headers, another as column headers, and aggregate values filling the cells.
What Is a Crosstab Query?
A regular SELECT query returns data in rows. A crosstab query rotates some of that data so it becomes column headers, giving you a compact, spreadsheet-style summary.
Example: You have a Sales table with columns for SalesPerson, Month, and Amount. A regular query shows:
| SalesPerson | Month | Amount |
|---|---|---|
| Alice | January | 5000 |
| Alice | February | 6200 |
| Bob | January | 4800 |
| Bob | February | 5100 |
A crosstab query transforms this into:
| SalesPerson | January | February |
|---|---|---|
| Alice | 5000 | 6200 |
| Bob | 4800 | 5100 |
Much easier to read and compare.
Creating a Crosstab Query with the Wizard
The easiest way to create a crosstab query is with the Query Wizard:
- Go to Create → Query Wizard
- Select Crosstab Query Wizard and click OK
- Choose the table or query that contains your data
- Select the field(s) for the row headings (e.g., SalesPerson)
- Select the field for the column headings (e.g., Month)
- Select the field to aggregate and choose the function (Sum, Count, Average, etc.)
- Optionally add a row total column
- Name the query and click Finish
Creating a Crosstab Query in SQL
Understanding the SQL syntax gives you more control. A crosstab query uses the TRANSFORM and PIVOT keywords, which are unique to Access SQL:
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Sales.SalesPerson
FROM Sales
GROUP BY Sales.SalesPerson
PIVOT Sales.Month;
Breaking this down:
- TRANSFORM — specifies the aggregate function and the value field
- SELECT — specifies the row heading field(s)
- FROM — the source table or query
- GROUP BY — groups the row headings
- PIVOT — specifies the field whose values become column headers
Controlling Column Order
By default, Access sorts the column headers alphabetically or by value. For months, this means April comes before January — not what you want.
You can specify the column order using IN after the PIVOT clause:
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Sales.SalesPerson
FROM Sales
GROUP BY Sales.SalesPerson
PIVOT Sales.Month IN ("January","February","March","April","May","June",
"July","August","September","October","November","December");
The IN clause also fixes the columns — without it, columns appear and disappear as data changes, which can break reports that depend on specific column names.
Using a Date Field as the Column Header
A common use case is pivoting by month from a date field. Use the Format() function to extract the month name:
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Sales.SalesPerson
FROM Sales
GROUP BY Sales.SalesPerson
PIVOT Format(Sales.SaleDate, "mmmm");
Or pivot by year:
PIVOT Format(Sales.SaleDate, "yyyy");
Or by quarter:
PIVOT "Q" & DatePart("q", Sales.SaleDate);
Adding Row Totals
To add a total column that sums across all column values, add a calculated field to the SELECT clause:
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Sales.SalesPerson, Sum(Sales.Amount) AS Total
FROM Sales
GROUP BY Sales.SalesPerson
PIVOT Sales.Month IN ("January","February","March");
The Sum(Sales.Amount) AS Total in the SELECT clause calculates the grand total for each row.
Limitations of Crosstab Queries
Fixed columns required for reports: If you use a crosstab query as the record source for a report, the columns must be fixed (use the IN clause). Otherwise, the report breaks when new column values appear.
Cannot be updated: Crosstab queries are read-only. You cannot edit data through a crosstab query.
Column count limit: Access has a practical limit of around 255 columns. If your pivot field has more than 255 distinct values, you will hit this limit.
Null values: Cells with no data show as blank (null). If you need zeros instead, wrap the TRANSFORM expression: TRANSFORM Nz(Sum(Sales.Amount), 0).
When to Use Crosstab Queries
Crosstab queries are ideal for:
- Monthly or quarterly sales summaries by salesperson or region
- Attendance or scheduling grids (employees as rows, dates as columns)
- Survey results (questions as rows, answer options as columns)
- Inventory by location (products as rows, warehouses as columns)
For anything more complex — like multiple levels of row grouping or dynamic column sets — consider exporting to Excel or using a reporting tool.
Conclusion
Crosstab queries are one of Access's most powerful reporting tools. Once you understand the TRANSFORM/PIVOT syntax, you can build compact, readable summaries from any relational dataset. Combine them with the IN clause for fixed columns and you have a reliable foundation for professional reports and dashboards.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Building crosstab queries in Access? Schedule a free call and get help designing your pivot-style reports.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on Queries & SQL
Totals Queries in Access: GROUP BY, COUNT, SUM, and More
Totals queries aggregate your data into summaries — counts, sums, averages, and more. Learn how to build them correctly and avoid the common mistakes.
UNION Queries in Access: Combining Data from Multiple Sources
UNION queries stack results from multiple SELECT statements into a single result set. Learn when and how to use them effectively in Microsoft Access.
Subqueries in Access SQL: Queries Within Queries
Subqueries let you use the result of one query inside another, enabling complex filtering and calculations that are impossible with simple JOINs.