TRANSFORM Statement (Microsoft Access SQL)

Creates a crosstab query.

Syntax

TRANSFORM aggfunctionselectstatement PIVOT pivotfield [IN (value1[, value2[, …]])]

The TRANSFORM statement has these parts:

Part	Description
aggfunction	An SQL aggregate function that operates on the selected data.
selectstatement	A SELECT statement.
pivotfield	The field or expression you want to use to create column headings in the query's result set.
value1, value2	Fixed values used to create column headings.

Remarks

When you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query.

TRANSFORM is optional but when included is the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE, that specify additional selection or sorting criteria. You can also use subqueries as predicates — specifically, those in the WHERE clause — in a crosstab query.

The values returned in pivotfield are used as column headings in the query's result set. For example, pivoting the sales figures on the month of the sale in a crosstab query would create 12 columns. You can restrict pivotfield to create headings from fixed values (value1, value2 ) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns.

Example

This example uses the SQL TRANSFORM clause to create a crosstab query showing the number of orders taken by each employee for each calendar quarter of 1994. The SQLTRANSFORMOutput function is required for this procedure to run.

Sub TransformX1() 
 
    Dim dbs As Database 
    Dim strSQL As String 
    Dim qdfTRANSFORM As QueryDef 
 
    strSQL = "PARAMETERS prmYear SHORT; TRANSFORM " _ 
        & "Count(OrderID) " _ 
        & "SELECT FirstName & "" "" & LastName AS " _ 
        & "FullName FROM Employees INNER JOIN Orders " _ 
        & "ON Employees.EmployeeID = " _ 
        & "Orders.EmployeeID WHERE DatePart " _ 
        & "(""yyyy"", OrderDate) = [prmYear] " 
   
       strSQL = strSQL & "GROUP BY FirstName & " _ 
        & """ "" & LastName " _ 
        & "ORDER BY FirstName & "" "" & LastName " _ 
        & "PIVOT DatePart(""q"", OrderDate)" 
     
    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("Northwind.mdb") 
 
    Set qdfTRANSFORM = dbs.CreateQueryDef _ 
        ("", strSQL) 
     
    SQLTRANSFORMOutput qdfTRANSFORM, 1994 
     
    dbs.Close 
 
End Sub

This example uses the SQL TRANSFORM clause to create a slightly more complex crosstab query showing the total dollar amount of orders taken by each employee for each calendar quarter of 1994. The SQLTRANSFORMOutput function is required for this procedure to run.

Sub TransformX2() 
 
    Dim dbs As Database 
    Dim strSQL As String 
    Dim qdfTRANSFORM As QueryDef 
 
    strSQL = "PARAMETERS prmYear SMALLINT; TRANSFORM " _ 
        & "Sum(Subtotal) SELECT FirstName & "" """ _ 
        & "& LastName AS FullName " _ 
        & "FROM Employees INNER JOIN " _ 
        & "(Orders INNER JOIN [Order Subtotals] " _ 
        & "ON Orders.OrderID = " _ 
        & "[Order Subtotals].OrderID) " _ 
        & "ON Employees.EmployeeID = " _ 
        & "Orders.EmployeeID WHERE DatePart" _ 
        & "(""yyyy"", OrderDate) = [prmYear] " 
    
       strSQL = strSQL & "GROUP BY FirstName & "" """ _ 
        & "& LastName " _ 
        & "ORDER BY FirstName & "" "" & LastName " _ 
        & "PIVOT DatePart(""q"",OrderDate)"         
         
    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("Northwind.mdb") 
 
    Set qdfTRANSFORM = dbs.CreateQueryDef _ 
        ("", strSQL) 
     
    SQLTRANSFORMOutput qdfTRANSFORM, 1994 
     
    dbs.Close 
 
End Sub 
 
Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _ 
    intYear As Integer) 
     
    Dim rstTRANSFORM As Recordset 
    Dim fldLoop As Field 
    Dim booFirst As Boolean 
 
    qdfTemp.PARAMETERS!prmYear = intYear 
    Set rstTRANSFORM = qdfTemp.OpenRecordset() 
     
    Debug.Print qdfTemp.SQL 
    Debug.Print 
    Debug.Print , , "Quarter" 
 
    With rstTRANSFORM 
        booFirst = True 
        For Each fldLoop In .Fields 
            If booFirst = True Then 
                Debug.Print fldLoop.Name 
                Debug.Print , ; 
                booFirst = False 
            Else 
                Debug.Print , fldLoop.Name; 
            End If 
        Next fldLoop 
        Debug.Print 
         
        Do While Not .EOF 
            booFirst = True 
            For Each fldLoop In .Fields 
                If booFirst = True Then 
                    Debug.Print fldLoop 
                    Debug.Print , ; 
                    booFirst = False 
                Else 
                    Debug.Print , fldLoop; 
                End If 
            Next fldLoop 
            Debug.Print 
            .MoveNext 
        Loop 
    End With 
     
End Function