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
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:
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
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.
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
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
Try our downloadable Access database demonstration of using
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 ...