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
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
Select Top
Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Query 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.

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:

Real Estate Marketing Campaign System
 









A Blue Claw Database Design Article:

Software Risk Assessment
 









Blue Claw Database Design Downloadable Tutorial:
TransferText & OutputTo Microsoft Access Download.  








Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
 


Contact Information

Copyright 2000-2017 Blue Claw Database Design