原作者:未知 翻译:周芳
运行记录集计算总和的例子
这是一个相当简单的方法,使用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%.