Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > Access数据库-模块/函数/VBA

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

时 间: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%.



Access软件网官方交流QQ群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助