Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
Crosstab Query in Access
SQL Crosstab Query
Home > Tutorials > Query Tutorials > Crosstab Query Code
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Access Tutorial


Access Crosstab Query Tutorial

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

Pivot Query

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



Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.

We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.


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:

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:

Crosstab Pivot Query

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.

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 ...







A Blue Claw Software Design Template:

Loan Process Management Access Database
 









A Blue Claw Database Design Article:

Microsoft Access On The Internet
 









Blue Claw Database Design Downloadable Tutorial:
Union Query (Advanced) Access Tutorial Download  








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


Contact Information
Copyright 2000-2017 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials