TRANSFORM Statement 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 SHORT; 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