Crosstab Query Example

Crosstab Query example with a slight twist to the simple Microsoft Access crosstab query that may not seem obvious to you.

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"
PIVOT 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:

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 cross tab query.

It will make the Access crosstab query simpler for you.

More Crosstab Query Examples, Discussion & Issues:

Crosstab Query Report in Microsoft Access

TRANSFORM Max(Q_Customer_order_item_Details.Expr1) AS Idetails SELECT Q_Customer_order_item_Details.order_item_ID FROM Q_Customer_order_item_Details ...

Histogram Query in Microsoft Access

You could bracket date ranges to simulate a crosstab query (pivot query). One advantage of using the method in this example over crosstabs is that you can ...

Crosstab Query Global Variable OutputTo Bug

Programming Microsoft Access Visual Basic: database VBA/VB script code help, samples.