TRANSFORM 语句示例 (DAO)
内容
此示例使用 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