TRANSFORM 语句示例

此示例使用 SQL TRANSFORM 子句创建交叉表查询显示 1994 年每一季每位员工所接的订单的数目。 该 SQLTRANSFORMOutput 函数对此过程的完成是必须的。

SubSub()

    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)"

    

    ' 在您的计算机中修改此行使其正确指到 Northwind 的路径。

    Set dbs = OpenDatabase("Northwind.mdb")

    ' 对运费超过 $100 的订单,

    Set qdfTRANSFORM = dbs.CreateQueryDef _

        ("", strSQL)

    

    SQLTRANSFORMOutput qdfTRANSFORM, 1994

    

    dbs.Close

End Sub

此示例使用 SQL TRANSFORM 子句创建一个稍微复杂的交叉表查询,来显示出每位员工在 1994 年所接订单的总金额。

SubSub()

    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)"        

        

    ' 在您的计算机中修改此行使其正确指到 Northwind 的路径。

    Set dbs = OpenDatabase("Northwind.mdb")

    ' 对运费超过 $100 的订单,

    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