Pivot Query How To

The pesky pivot query.  For this Access coding example let us assume 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 pivot 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]
PIVOT 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 PIVOT 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 pivot query is not support.  There are workarounds which are similar to the functioning of the Histogram Example.