AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890


Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at

Are you outside the United States?  We can ship multiple books worldwide in most countries.




The structure of a crosstab query in detail

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here

A crosstab query in detail

Find total sales by state and year


As you can see from the design view and the SQL code, we use three fields to create this very informative crosstab query.  The first is the state field, which we use as the row heading.  The second is the OrderDate field, which we use for column headings.  For the OrderDate field there is a catch:  We use the year() function to extract only the year out of the multitude of order dates we have in the database.  The third field is the value field, which we call OrderTotal.  This is a calculated field that sums the unitprice * the quantity of each product for each of our customer orders. 

When you look at the SQL code, you will notice the use of a new statement called TRANSFORM.  The TRANSFORM statement relates to the value field in a crosstab query, and it always precedes the SQL statement.  Additionally, it is always followed by the aggregate function used for the value field, which in this case is sum(Quantity * UnitPrice).  Below is the SQL code for the crosstab query explaining it step by step: 

TRANSFORM Sum([unitprice]*[quantity]) AS Ordertotal

The TRANSFORM statement is used for the value field in a crosstab query. 

SELECT State FROM Qry_Crosstab_Base

The SELECT statement is used only for the row-heading field of the crosstab query, which in this case is the State field. 


The GROUP BY clause follows next, and it is applied on the same field used in the SELECT statement.  Since we used State for the SELECT statement, we use State for the GROUP BY clause as well. 

PIVOT Year([OrderDate])

The PIVOT statement is the last one used in a crosstab query, and it is applied on the column-heading field. 

Below is the general structure of a crosstab query in SQL code. 


SELECT ColumnField

FROM DataSource

GROUP BY ColumnField

PIVOT RowField

Design Code:


TRANSFORM sum([unitprice]*[quantity]) AS Ordertotal


FROM Qry_Crosstab_Base


PIVOT Year([OrderDate]) 

Looking at the results below, we can quickly understand and compare sales volumes by each state and each year.  For example, the biggest sales revenues come from California, and they grow year by year.  In Florida, we need to have a look at what is happening because sales dropped a lot for 2010.  This is the power of crosstab queries. We get summarized results in seconds.