Create a Crosstab Query in Microsoft Access

A crosstab query summarizes values in a matrix. One field supplies the row headings, another supplies the column headings, and an aggregate such as Sum, Count, or Avg supplies the values at their intersections.

Last updated: August 28, 2026.

Example: monthly sales by region

Assume a Sales table contains Region, OrderDate, and Amount. This query produces one row per region and one column per month:

TRANSFORM Sum(Nz([Amount], 0)) AS TotalSales
SELECT Region
FROM Sales
WHERE OrderDate >= DateSerial(Year(Date()), 1, 1)
  AND OrderDate < DateSerial(Year(Date()) + 1, 1, 1)
GROUP BY Region
PIVOT Format([OrderDate], "mmm")
  IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun",
      "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");

The TRANSFORM expression calculates the values. Region is the row heading, and the formatted order month is the column heading.

Why specify the month columns?

The IN list fixes the order and presence of the headings. Without it, Access may omit a month that has no records, and the available columns can change from one run to another. Fixed headings make the query safer as the record source for reports and forms.

Create it with the Crosstab Query Wizard

1. Select Create → Query Wizard.
2. Choose Crosstab Query Wizard.
3. Select the source table or query.
4. Choose the field used for row headings.
5. Choose the field used for column headings and select a date interval if appropriate.
6. Choose the value field and aggregate function.
7. Open the finished query in Design View or SQL View to add criteria and fixed headings.

Use typed parameters in a crosstab query

Crosstab parameters should be declared explicitly. This example asks for a start date and an inclusive end date:

PARAMETERS [Enter start date:] DateTime,
           [Enter end date:] DateTime;
TRANSFORM Sum(Nz([Amount], 0)) AS TotalSales
SELECT Region
FROM Sales
WHERE OrderDate >= [Enter start date:]
  AND OrderDate < DateAdd("d", 1, [Enter end date:])
GROUP BY Region
PIVOT Format([OrderDate], "mmm");

In Design View, open Design → Parameters, enter the prompts exactly as they appear in the criteria, and select the Date/Time type.

Replace empty results with zero

Nz([Amount], 0) treats a missing amount as zero before summing. This does not automatically create missing row-and-column combinations. If a report requires every possible combination, use supporting tables for the required rows and columns.

Start with a select query when joins are complicated

If the data comes from several joined tables, first create a select query that returns the row field, column field, and value field. Use that saved query as the crosstab’s source. This keeps the transformation easy to test.

Microsoft provides additional design examples in its crosstab query guide.