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