GROUP BY Column1
What does it do?
In order to create a PIVOT query with access SQL, a TRANSFORM statement must be used.
After the TRANSFORM an aggregate function like SUM or COUNT must be used with the desired
column to be totaled or averaged(depending on function).
Then for the SELECT statment use the column that is desired for the grouping and also place that column
in the GROUP BY statement.
Finally in the PIVOT statement use the desired column that will be used to categorize the
aggregation that is performed in the TRANSFORM statement; this will create a column for each
unique value/string in the table that exists in that column.
GROUP BY CustomerID
PIVOT 'Item Number ' &
In this example, we are using a PIVOT query to see how what the total quantity
of the different item types each customer ordered.
This "Item Number" text is being included wit the PIVOT for more appropriate headers.
Blue - Is used to identify reserved keywords/characters that are used
by the SQL engine, i.e. SELECT, FROM, or WHERE.
Purple - Is used to identify portions of the SQL statement
that are defined by the user, i.e. Column Names, Table Names, Inputed Values.