(Guest blogger, Molly Pell is a Senior Systems Analyst at FMS, Inc., a leading Access applications company, which designs custom solutions to improve operations for small and large organizations in all sectors.)
A well designed database stores data in a normalized format with dates defined in a field so that new data is simply added as additional records. However, people want to see data with dates grouped by columns.
This can be done by using a crosstab query. However, when creating reports based on crosstab queries, we need to control the specific column names that are returned by the query. Otherwise, the report cannot refer to the query’s fields.
As an example, we will use a slightly modified version of the Northwind 2007 Template. The following crosstab summarizes the sales for previous year, with a column for each month:
The query sql looks like this:
TRANSFORM Sum(CCur([Quantity]*[Unit Price]*(1-[Discount]))) AS Price
SELECT [Order Details].[Product ID]
FROM [Order Summary] INNER JOIN [Order Details] ON [Order Summary].[Order ID] = [Order Details].[Order ID]
WHERE (((Format([Order Date],"yyyy"))=Format(Date(),"yyyy")-1))
GROUP BY [Order Details].[Product ID]
ORDER BY Format([Order Date],"mmm")
PIVOT Format([Order Date],"mm");
This works great until you run into a month that doesn’t have any sales. For example, assume that you have no sales in July:
Notice that in addition to being sorted undesirably, there is no column for July. This poses a problem, for example, if you want to use the crosstab query as the datasource for a report, Access 2010 reports reference field names directly. If you want to use a crosstab query as the RecordSource of a report, its column names should be static.
Use the PIVOT IN Clause to Specify Required Column Names
In Design view, open the Property Sheet for the query, and set the Column Headings property to show the desired columns in the desired order:
The query sql looks like this:
TRANSFORM Sum(CCur([Quantity]*[Unit Price]*(1-[Discount]))) AS Price
SELECT [Order Details].[Product ID]
FROM [Order Summary] INNER JOIN [Order Details] ON [Order Summary].[Order ID] = [Order Details].[Order ID]
WHERE (((Format([Order Date],"yyyy"))=Format(Date(),"yyyy")-1))
GROUP BY [Order Details].[Product ID]
ORDER BY Format([Order Date],"mmm")
PIVOT Format([Order Date],"mmm") IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
Now that you have static column names, you can use the query to drive a report, referring to the known query names:
For more information about using crosstab queries on reports, check out our tip here http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html.
