%dim crumb,crumb2 crumb2="Crosstab Query in Access" crumb="Crosstab Query Code" crumb1="SQL Crosstab Query" %>
|
![]() Query Tutorials Index:
|
Access Crosstab Query TutorialCrosstab query coding example assumes we have a table with the following layout:
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:
|
Microsoft Office: MS Access 2000 Through Access 2016 & Office 365 |
Contact Information <% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %> Development in Microsoft Access, Microsoft SQL/Server and Azure See our SQL/Server Development and Access Migration Tutorials
|