Access Crosstab Query Tutorial

Crosstab query coding example assumes we have a table with the following layout:

Our goal is to total sales (Amount) by month (Sale_Date) and Department with months as column headings and departments as row headings.

Access provides a crosstab query wizard which, once you get some practice you’ll find it as good place to start.  I usually have to go into the SQL editor and tweak the code a little. Here are the SQL statements that will produce the output we want:

TRANSFORM Sum([M_Sales].[Amount]) AS SumOfAmount
SELECT [M_Sales].[Department]
FROM M_Sales
GROUP BY [M_Sales].[Department]
CROSSTAB Format([M_Sales].[Sale_date],”mmm”) In (“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”);

The key points of the query are:

New! Download Crosstab/Crosstab Query Example Database

1)  The query’s TRANSFORM statement contains the values that will be summed and displayed as the results of the query.

2) The SELECT statement will contain the Row Headings

3) The GROUP BY is just what it says 4) The CROSSTAB statement will create the column headings and may be thought of as a Horizontal Group By. Here are the results of the query:

Warning about upsize to SQL Server – Access crosstab query is not support.

Additional Features of the Crosstab Query

Here is another Crosstab Query example with a slight twist to the simple example shown above.In this query example we want only the totals by month and we don’t care about department.

TRANSFORM Sum([M_Sales].[Amount]) AS SumOfAmount
SELECT “Total” AS Total
FROM M_Sales
GROUP BY “Total”
CROSSTAB Format([M_Sales].[Sale_date],”mmm”) In (“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”);

We need to select something so I put in the constant ‘Total’ so that the query will run.  We don’t want to see ‘Total’ in the first column so we will hide this column.  Here are the results:

The crosstab command in the crosstab query causes the data field listed to be transformed into column headings – this is the main feature and function of the crosstab query.

It is possible to create the same affect by grouping the data using a technique similar to our Histogram Query.

Tip:  If you need to do some restriction to select only certain records then create that query first and use it as the input to the crosstab query. New! Crosstab Query Download

It will make the Access crosstab query simpler for you.

Crosstab Error

Apparent Microsoft Access Bug with Global Variables & OutputTo Using Crosstab Query Docmd Method

We have recently encountered what appears to be a bug in Microsoft Access 2000, 2002 & 2003. This bug occurs when we setup a query with global variables in the criteria (where clause).  Then we use this query as input to a crosstab query.  The next step is to use the Docmd.OutputTo command to send the results of the cross tab out as an Excel spreadsheet.  The bug is centered on the OutputTo method.

The easiest work around we found was to change the crosstab query to a make table query and then export the temporary table to Excel – this method works fine.

There are other bugs with crosstab queries such as not being able to reference form fields as criteria in the crosstab or queries supplying data to the crosstab query.

Try our downloadable Access database demonstration of using Global variables as query parameters.

More Crosstab Query Examples, Discussion & Issues:

Access Crosstab Query Report in Microsoft Access

Histogram Crosstab Query in Microsoft Access
You could bracket date ranges to simulate a crosstab query (crosstab query). One advantage of using the method in this example over crosstabs is that you can …

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016