如何根据[分类]字段给[排名]字段赋值？ - Office中国论坛/Access中国论坛

Private Sub CommandFind_Click()
Dim rs As New ADODB.Recordset
Dim strfind, strBiaoname As String
Dim strTup, strTdown As Date
'清除子窗体数据源
Me.ChildShow.SourceObject = ""
If Me.ComboBiao.Value <> "" Then
strTup = Me.TextTup.Value
strTdown = Me.TextTDown.Value
strBiaoname = DLookup("表名", "学生成绩_表名", "说明 = '" & Me.ComboBiao.Value & "'")
'批量删除临时表
Dim tbl As DAO.TableDef
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) = "临时表_" Then
DoCmd.DeleteObject acTable, tbl.Name
End If
Next
'删除指定表
'strfind = "drop table 临时表_成绩查询"
'rs.Open strfind, CurrentProject.Connection, 1, 3
'建立临时表
strfind = "select 学生档案.班级,学生档案.姓名," & strBiaoname & ".语文," & strBiaoname & ".数学," & strBiaoname & ".英语 into 临时表_成绩查询 from 学生档案 INNER JOIN " & strBiaoname & " ON 学生档案.ID = " & strBiaoname & ".ID where 学生档案.入学时间 between # " & strTup & " # and #" & strTdown & "# "
rs.Open strfind, CurrentProject.Connection, 1, 3
'建立新字段
strfind = "alter table 临时表_成绩查询 add 总分 smallint ,排名 smallint"
rs.Open strfind, CurrentProject.Connection, 1, 3
'总分字段赋值
strfind = "UPDATE 临时表_成绩查询 SET 总分 = 语文+数学+英语"
rs.Open strfind, CurrentProject.Connection, 1, 3
'排名字段赋值
'直接排名并更新：update 临时表_成绩查询 a set 排名=dcount('总分','临时表_成绩查询','总分>' & a.总分) +1
'根据班级字段排名并更新：？
strfind = "update 临时表_成绩查询 a set 排名=dcount('总分','临时表_成绩查询','总分>' & a.总分) +1"
rs.Open strfind, CurrentProject.Connection, 1, 3
'更新子窗体数据源
Me.ChildShow.SourceObject = "表.临时表_成绩查询"
'子窗体数据按指定字段排序
Me.ChildShow.Form.RecordSource = "select * from 临时表_成绩查询 order by 班级,排名"
'刷新子窗体显示
Me.ChildShow.Requery
Set rs = Nothing
Else
MsgBox "没有选择考试时间！"
End If
End Sub

复制代码