Syntax:
TRANSFORM Aggregate(Column2) SELECT Column1 FROM TableName GROUP BY Column1 PIVOT Column3;
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.
Example:
TRANSFORM SUM(Quantity) SELECT CustomerID FROM Orders GROUP BY CustomerID PIVOT 'Item Number ' & ItemID;
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.
Results:
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.