Query Tutorials Index:
Access Crosstab Query Tutorial
Crosstab query coding example assumes we have a table with the following layout:
Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.
Are you looking for an MS Access template database? We have many for sale in our Access Template and Examples Software section.
Access provides a crosstab 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
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 CROSSTAB statement
will create the column headings and may be thought of as a Horizontal Group
By. Here are the results of the query:
Copyright 2000-2018 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure
See our SQL/Server Development and Access Migration Tutorials