Expert guide

Microsoft Access Crosstab Query Guide

Turn rows into a grid of months, regions or products, ready for reports

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: a crosstab uses TRANSFORM aggregate SELECT row-heading FROM ... GROUP BY row-heading PIVOT column-heading. Fix the columns with PIVOT ... IN (...) so they always exist and stay in order, declare any form references with a PARAMETERS clause, wrap the aggregate in Nz to show zeros, and add a row total by including the aggregate again in the SELECT. Report-ready crosstabs are a regular part of our Access reports and forms work.

Managers think in grids: sales by month across the top and region down the side, jobs by status per technician, defects by type per supplier. Access stores data in rows. The crosstab query bridges the two, and once its few quirks are understood, it is one of the most useful tools in Access.

The Anatomy of a Crosstab

TRANSFORM Sum(l.Qty * l.UnitPrice) AS Sales          -- value in each cell
SELECT c.Region                                       -- row heading(s)
FROM (tblOrders AS o
      INNER JOIN tblOrderLines AS l ON o.OrderID = l.OrderID)
      INNER JOIN tblCustomers AS c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= #2026-01-01# AND o.OrderDate < #2027-01-01#
GROUP BY c.Region
PIVOT Format(o.OrderDate, "mmm");                      -- column heading
ClauseRole
TRANSFORMThe aggregate shown in each cell
SELECT ... GROUP BYRow headings (one or more fields)
PIVOTThe field whose values become columns
WHEREFilters before aggregation

Fix Column Order and Presence

PIVOT Format(o.OrderDate, "mmm")
  IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Without IN, columns appear alphabetically (Apr, Aug, Dec...) and months with no sales disappear. With IN, every month is always present, in calendar order, which is what reports and exports need. The same idea applies to status columns: IN ("Open","Scheduled","In progress","Completed").

Zeros Instead of Blanks

TRANSFORM Nz(Sum(l.Qty * l.UnitPrice), 0) AS Sales

Row Totals

TRANSFORM Nz(Sum(l.Qty * l.UnitPrice), 0) AS Sales
SELECT c.Region, Sum(l.Qty * l.UnitPrice) AS [Year Total]
FROM ...
GROUP BY c.Region
PIVOT Format(o.OrderDate, "mmm") IN ("Jan","Feb", ... ,"Dec");

Any aggregate in the SELECT list, besides the row headings, becomes an extra column calculated across the whole row.

Parameters From a Form

PARAMETERS [Forms]![frmReports]![txtStart] DateTime,
           [Forms]![frmReports]![txtEnd] DateTime;
TRANSFORM Nz(Count(j.JobID), 0) AS Jobs
SELECT t.TechName
FROM tblJobs AS j INNER JOIN tblTechs AS t ON j.TechID = t.TechID
WHERE j.ScheduledOn Between [Forms]![frmReports]![txtStart]
                        And [Forms]![frmReports]![txtEnd]
GROUP BY t.TechName
PIVOT j.Status IN ("Open","Scheduled","In progress","Completed");

The PARAMETERS line is mandatory for crosstabs that reference form controls. Normal select queries tolerate undeclared form references; crosstabs do not.

Multiple Values per Cell

A crosstab allows only one TRANSFORM value. To show, for example, both sales and margin by month, create two crosstabs and join them on the row heading in a third query, or use a select query with conditional aggregates instead:

SELECT c.Region,
  Sum(IIf(Month(o.OrderDate)=1, l.Qty*l.UnitPrice, 0)) AS Jan_Sales,
  Sum(IIf(Month(o.OrderDate)=1, l.Qty*(l.UnitPrice-l.UnitCost), 0)) AS Jan_Margin,
  ...
FROM ...
GROUP BY c.Region;

Conditional aggregates are more typing but behave like ordinary queries and translate directly to SQL Server.

Crosstabs and Reports

  • Always fix column headings, so report controls bind to stable field names (Jan, Feb...).

  • For rolling periods (last 12 months), pivot on a relative label such as M1 to M12 calculated from the date, and show real month names in report labels with expressions.

  • Add report-level totals in the report footer with =Sum([Jan]) and so on.

Relative month label for a rolling 12-month crosstab:
PIVOT "M" & (DateDiff("m", o.OrderDate, Date()) + 1)
  IN ("M12","M11","M10","M9","M8","M7","M6","M5","M4","M3","M2","M1");

Crosstab or Excel PivotTable?

NeedCrosstab queryExcel PivotTable
Fixed, repeatable reportYesPossible
Ad hoc slicing by usersLimitedExcellent
Feeding an Access reportYesNo
Large data in the databaseEfficientNeeds export or connection

For ad hoc questions in plain English instead of building crosstabs, see natural-language queries on Access; for writing crosstab SQL with AI, see ChatGPT for Access SQL.

FAQ

Access crosstab query FAQ

Columns, parameters and reports

A crosstab query summarises data in a grid: one field's values become row headings, another field's values become column headings, and a third field is aggregated (Sum, Count, Avg) in the cells. It is the Access equivalent of a simple Excel PivotTable, written in SQL with TRANSFORM and PIVOT.

List the column values in the PIVOT clause with IN, for example PIVOT Format([OrderDate],"mmm") IN ("Jan","Feb","Mar",...), or set the Column Headings property in the query's property sheet. Fixed headings also guarantee columns exist even when a month has no data, which reports need.

Crosstab queries require parameters to be declared explicitly. Add a PARAMETERS clause at the top, such as PARAMETERS [Forms]![frmReports]![txtStart] DateTime;, or declare them in Query → Parameters. Without the declaration Access raises 'The Microsoft Access database engine does not recognize ... as a valid field name or expression'.

Wrap the aggregate in Nz: TRANSFORM Nz(Sum([Amount]),0) AS Total. Nz returns a Variant, so for numeric formatting in reports you may also use CCur or Val around it.

Yes, as long as the column headings are fixed with IN or Column Headings. Reports bind controls to specific field names, so a crosstab whose columns change from month to month breaks the report. Fixed headings solve that.