Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Query Examples

Crosstab Query in Access SQL SQL Queries

Access Database Tutorials>SQL Queries>Crosstab Query Advanced

Download Access Program


Aggregate Functions
Child/Parent Table Query
Choose Function
SQL Choose Function +
Crosstab/Pivot Query
SQL Crosstab Query +
DateTime Query
SQL Delete Query
External Link to Tables
Filter Report Records
Group By Clause
Having Clause Query
Histogram Query
Insert Into Query/Append
Master/Detail Updates
SQL Order By Dynamic
Parameter Query
SQL Predicate Example
Self Join Query
Access Scalar Query
Select Statement
Select Top 1 Query
Union Query Example
Update Query Example


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:

Crosstab Query Example

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.
 


 

We now offer web-based SQL/Server application development in partnership with Integrated Databases, Inc. (new projects only).



Contact Information

SQL Queries
MS Access SQL Queries 2007 2003 2000  All Windows Versions