Crosstab Queries in Access: Turn Rows Into Columns

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:

1. Go to Create → Query Wizard
2. Select Crosstab Query Wizard and click OK
3. Choose the table or query that contains your data
4. Select the field(s) for the row headings (e.g., SalesPerson)
5. Select the field for the column headings (e.g., Month)
6. Select the field to aggregate and choose the function (Sum, Count, Average, etc.)
7. Optionally add a row total column
8. 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.