当前位置: 首页 > 文档资料 > SQL 合计函数 >

TRANSFORM 语句示例 (DAO)

优质
小牛编辑
123浏览
2023-12-01

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