Office中国论坛/Access中国论坛
标题:
如何根据[分类]字段给[排名]字段赋值?
[打印本页]
作者:
秒弑
时间:
2012-1-6 08:15
标题:
如何根据[分类]字段给[排名]字段赋值?
如题。源代码如下:
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
复制代码
请教如何根据“班级”分类计算排名并更新呢?
我写的是:
strfind = "update 临时表_成绩查询 a set 排名=dcount('总分','临时表_成绩查询','总分>' & a.总分) +1"
复制代码
但是只能无分类排名,要怎么修改?
源文打包如下:
[attach]47962[/attach]
作者:
Henry D. Sy
时间:
2012-1-6 09:22
strfind = "update 临时表_成绩查询 a set 排名=dcount('总分','临时表_成绩查询','班级='"& 班级 &"' AND 总分>' & a.总分) +1"
作者:
秒弑
时间:
2012-1-9 09:13
[attach]48006[/attach]
修改后报错,请问H大这是怎么回事?{:soso_e132:}
作者:
秒弑
时间:
2012-1-9 09:42
H大,求助!
作者:
秒弑
时间:
2012-1-30 00:07
H大,求助!
作者:
aslxt
时间:
2012-1-30 15:30
strfind = "update 临时表_成绩查询 a set 排名=dcount(" & """" & "总分" & """" & "," & """" & "临时表_成绩查询" & """" & "," & """" & "班级='" & """" & " & a.班级 & " & """" & "' AND 总分>" & """" & " & a.总分) +1"
作者:
p51219
时间:
2016-7-8 07:19
好好好好好好好好好好好
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3