MS-Access / Getting Started

---

TRANSFORM Statement

In a desktop database, produces a crosstab query that lets you summarize a single value by using the values found in a specified column or in an expression as the column headers and using other columns or expressions to define the grouping criteria to form rows. The result looks similar to a spreadsheet and is most useful as input to a graph object. This is an Access extension to standard SQL.

Syntax

TRANSFORM <aggregate-function-expression>
  <select-statement>
PIVOT <expression>
[IN (<column-value-list>)]

where <aggregate-function-expression> is an expression created with one of the aggregate functions, <select-statement> contains a GROUP BY clause, and <column-value-list> is a list of required values expected to be returned by the PIVOT expression, enclosed in quotes and separated by commas. (You can use the IN clause to force the output sequence of the columns.)

Notes: The <aggregate-function-expression> parameter is the value that you want to appear in the "body" of the crosstab datasheet. PIVOT <expression> defines the column or expression that provides the column headings in the crosstab result. You might, for example, use this value to provide a list of months with aggregate rows defined by product categories in the <select-statement> GROUP BY clause. You can use more than one column or expression in the SELECT statement to define the grouping criteria for rows.

Example

To produce a total sales amount for each month in the year 2010, categorized by product, enter the following (qxmpl2010SalesByProductXtab):

TRANSFORM Sum(tblContactProducts.SoldPrice) AS SumOfSoldPrice
 SELECT tblProducts.ProductID, tblProducts.ProductName,
  Sum(tblContactProducts.SoldPrice) AS TotSales
 FROM tblProducts
  INNER JOIN tblContactProducts
  ON tblProducts.ProductID = tblContactProducts.ProductID
 GROUP BY tblProducts.ProductID, tblProducts.ProductName
PIVOT Format([DateSold],"mmm yyyy")
 IN ("Jan 2010","Feb 2010","Mar 2010","Apr 2010","May 2010",
  "Jun 2010","Jul 2010","Aug 2010","Sep 2010",
  "Oct 2010","Nov 2010","Dec 2010");

Note This example shows a special use of the IN predicate to define not only which months should be selected but also the sequence in which Access displays the months in the resulting recordset.