【译文】运行记录集计算总和的例子

时　间:2012-04-11 10:54:07
作　者:周芳   ID：24526  城市：上海
摘　要:使用DAO记录集来计算总和
正　文:

原作者：未知　　翻译：周芳

运行记录集计算总和的例子

    这是一个相当简单的方法,使用DAO记录集来计算总和。首先，我们创造一个临时表，里面分别有字段“subid、absenceDate、Day_Count、running_sum"

    我们的目的是计算基于老师缺席而代课老师工作的总量，（一个老师缺席了，另一个代课老师上班）

    在这个例子中，我们要用到rst!day_count而不只是计算记录，因为代课老师可能工作半天也可能工作全天。这里是VBA代码：
   
'假定例子数据库中有一个表t_Sub_Pay，t_Sub_Pay表中有字段subid、absencedate、Day_Count、running_sum

Private Sub Sum_Button_Click()
    On Error GoTo Err_Sum_Button_Click
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim hold_subid As Long
    Dim hold_day_Count As Long
    Dim sqltext As String
    Dim wksp As DAO.Workspace
    Set db = CurrentDb
    'calculate running sum of days worked.计算工作天数的总和
    Set wksp = DBEngine.Workspaces(0)
    wksp.BeginTrans
    Set rst = db.OpenRecordset("Select * from t_Sub_Pay order by subid,absencedate") '注意这里按subid进行了排序，如果不按subid排序，后面的统计就会出错。
    rst.MoveFirst

    hold_subid = rst!SubID
    hold_day_Count = 0
    '
    Do While Not rst.EOF
        If hold_subid <> rst!SubID Then
            '注意,当遇到一个新的代课老师，我们就重新计算一次
            '新的代课老师
            hold_day_Count = rst!Day_Count
            hold_subid = rst!SubID
        Else
            hold_day_Count = hold_day_Count + rst!Day_Count
        End If
        rst.Edit
        rst!running_sum = hold_day_Count    '这里计算总和
        rst.Update
        rst.MoveNext
    Loop
    wksp.CommitTrans
    rst.Close
    Set rst = Nothing
    wksp.Close
    Exit Sub

Err_Sum_Button_Click:
    wksp.Rollback    ' 如果报错就回滚
    rst.Close
    Set rst = Nothing
    wksp.Close
    Resume quit_it
quit_it:
End Sub
.
注意,这个例子也可以用于事务处理——在这种情况下计算连续的总量和时间大约减少了75%。

【原文】

Calculate Running Sum Recordset Example

Here is a fairly simple way to calculate a running sum using a DAO Recordset operation. To begin, we have created a temporary table with Absence Dates, and Substitute Teach ID (SubID) - this table was created via a previous query not shown in this example.
Our goal is to determine running sum of substitute records based on absence date (teacher was absent and therefore a sub worked in this day).

In the example we used rst!day_count rather than just counting records because the sub can work either 1/2 days or full days (1).  Here is the VBA code:

Private Sub Sum_Button_Click()
    On Error GoTo Err_Sum_Button_Click
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim hold_subid As Long
    Dim hold_day_Count As Long
    Dim sqltext As String
    Dim wksp As DAO.Workspace
    Set db = CurrentDb
    'calculate running sum of days worked.

    Set wksp = DBEngine.Workspaces(0)
    wksp.BeginTrans
    Set rst = db.OpenRecordset("Select * from t_Sub_Pay order by subid,absencedate") '

    rst.MoveFirst

    hold_subid = rst!SubID
    hold_day_Count = 0
    '
    Do While Not rst.EOF
        If hold_subid <> rst!SubID Then     

            hold_day_Count = rst!Day_Count
            hold_subid = rst!SubID
        Else
            hold_day_Count = hold_day_Count + rst!Day_Count
        End If
        rst.Edit
        rst!running_sum = hold_day_Count    '

        rst.Update
        rst.MoveNext
    Loop
    wksp.CommitTrans
    rst.Close
    Set rst = Nothing
    wksp.Close
    Exit Sub

Err_Sum_Button_Click:
    wksp.Rollback    ' cancel transactions if there is an error

    rst.Close
    Set rst = Nothing
    wksp.Close
    Resume quit_it
quit_it:
End Sub

Note that this example also uses Transaction Processing - in this case the time to do the running sum processing was reduced by about 75%.