本人在编写一套成绩管理系统时,在给一个班级的学生的成绩排名次时遇到如下问题:
我的成绩是从一个表中选择出来的,表的结构为:学期、类型、年级、班级、学生姓名、科目、成绩.
而现在我要对班级的成绩做统计,不同班级和不同学期的课程数都不相同,我以下的代码只能把该班级的科目、总分、平均分在一个MSFlexGrid中显示出来,名次就排不出来。代码如下:
txtSQL = "select distinct class_name,student_name,term from qzl_grade where class_name ='" & Trim$(Combo1(1).Text) & "' and term='" & Trim$(Combo1(2).Text) & "' order by class_name,student_name"
Set mrc1 = ExecuteSQL(txtSQL, msg)
If mrc1.EOF = True Then
MsgBox "还没有满足条件的成绩,请联系该门课程的授课教师是否已经输入考试成绩!", vbOKOnly + vbInformation, "提示"
Exit Sub
End If
sum = 0
For i = 1 To mrc1.RecordCount
txtSQL = "select term,class_name,student_name,kind,course_name,z_grade from qzl_grade where class_name='" & Trim$(mrc1.Fields(0)) & "'and student_name='" & Trim$(mrc1.Fields(1)) & "'"
Set mrc2 = ExecuteSQL(txtSQL, msg)
If i = 1 Then
MSF1.Cols = mrc2.RecordCount + 8
MSF1.TextMatrix(0, 1) = "学期"
MSF1.TextMatrix(0, 2) = "班级"
MSF1.TextMatrix(0, 3) = "姓名"
MSF1.TextMatrix(0, 4) = "类型"
g = 5
For j = 1 To mrc2.RecordCount
MSF1.TextMatrix(0, g) = mrc2.Fields("course_name")
g = g + 1
mrc2.MoveNext
Next j
MSF1.TextMatrix(0, g) = "总分"
MSF1.TextMatrix(0, g + 1) = "平均分"
MSF1.TextMatrix(0, g + 2) = "名次"
MSF1.ColWidth(0) = 350
MSF1.ColWidth(1) = 2200
MSF1.ColWidth(2) = 800
MSF1.ColWidth(3) = 800
MSF1.ColWidth(4) = 600
For r = 1 To mrc2.RecordCount + 7
MSF1.ColAlignment(r) = 0
Next r
mrc2.MoveFirst
MSF1.Rows = 30
MSF1.Row = 1
End If
MSF1.Rows = MSF1.Rows + 1
MSF1.TextMatrix(MSF1.Row, 1) = mrc2.Fields(0)
MSF1.TextMatrix(MSF1.Row, 2) = mrc2.Fields(1)
MSF1.TextMatrix(MSF1.Row, 3) = mrc2.Fields(2)
MSF1.TextMatrix(MSF1.Row, 4) = mrc2.Fields(3)
g = 5
sum = 0
For j = 1 To mrc2.RecordCount
MSF1.Col = g
If Val(mrc2.Fields("z_grade")) < 60 Then
MSF1.CellForeColor = vbRed
End If
MSF1.TextMatrix(MSF1.Row, g) = mrc2.Fields("z_grade")
sum = sum + mrc2.Fields("z_grade")
g = g + 1
mrc2.MoveNext
Next j
MSF1.TextMatrix(MSF1.Row, g) = sum
avg1 = Int(sum / (mrc2.RecordCount) * 10 + 0.5) / 10
MSF1.Col = g + 1
If avg1 < 60 Then
MSF1.CellForeColor = vbRed
End If
MSF1.TextMatrix(MSF1.Row, g + 1) = avg1
MSF1.Row = MSF1.Row + 1
mrc1.MoveNext
Next i
我的成绩是从一个表中选择出来的,表的结构为:学期、类型、年级、班级、学生姓名、科目、成绩.
而现在我要对班级的成绩做统计,不同班级和不同学期的课程数都不相同,我以下的代码只能把该班级的科目、总分、平均分在一个MSFlexGrid中显示出来,名次就排不出来。代码如下:
txtSQL = "select distinct class_name,student_name,term from qzl_grade where class_name ='" & Trim$(Combo1(1).Text) & "' and term='" & Trim$(Combo1(2).Text) & "' order by class_name,student_name"
Set mrc1 = ExecuteSQL(txtSQL, msg)
If mrc1.EOF = True Then
MsgBox "还没有满足条件的成绩,请联系该门课程的授课教师是否已经输入考试成绩!", vbOKOnly + vbInformation, "提示"
Exit Sub
End If
sum = 0
For i = 1 To mrc1.RecordCount
txtSQL = "select term,class_name,student_name,kind,course_name,z_grade from qzl_grade where class_name='" & Trim$(mrc1.Fields(0)) & "'and student_name='" & Trim$(mrc1.Fields(1)) & "'"
Set mrc2 = ExecuteSQL(txtSQL, msg)
If i = 1 Then
MSF1.Cols = mrc2.RecordCount + 8
MSF1.TextMatrix(0, 1) = "学期"
MSF1.TextMatrix(0, 2) = "班级"
MSF1.TextMatrix(0, 3) = "姓名"
MSF1.TextMatrix(0, 4) = "类型"
g = 5
For j = 1 To mrc2.RecordCount
MSF1.TextMatrix(0, g) = mrc2.Fields("course_name")
g = g + 1
mrc2.MoveNext
Next j
MSF1.TextMatrix(0, g) = "总分"
MSF1.TextMatrix(0, g + 1) = "平均分"
MSF1.TextMatrix(0, g + 2) = "名次"
MSF1.ColWidth(0) = 350
MSF1.ColWidth(1) = 2200
MSF1.ColWidth(2) = 800
MSF1.ColWidth(3) = 800
MSF1.ColWidth(4) = 600
For r = 1 To mrc2.RecordCount + 7
MSF1.ColAlignment(r) = 0
Next r
mrc2.MoveFirst
MSF1.Rows = 30
MSF1.Row = 1
End If
MSF1.Rows = MSF1.Rows + 1
MSF1.TextMatrix(MSF1.Row, 1) = mrc2.Fields(0)
MSF1.TextMatrix(MSF1.Row, 2) = mrc2.Fields(1)
MSF1.TextMatrix(MSF1.Row, 3) = mrc2.Fields(2)
MSF1.TextMatrix(MSF1.Row, 4) = mrc2.Fields(3)
g = 5
sum = 0
For j = 1 To mrc2.RecordCount
MSF1.Col = g
If Val(mrc2.Fields("z_grade")) < 60 Then
MSF1.CellForeColor = vbRed
End If
MSF1.TextMatrix(MSF1.Row, g) = mrc2.Fields("z_grade")
sum = sum + mrc2.Fields("z_grade")
g = g + 1
mrc2.MoveNext
Next j
MSF1.TextMatrix(MSF1.Row, g) = sum
avg1 = Int(sum / (mrc2.RecordCount) * 10 + 0.5) / 10
MSF1.Col = g + 1
If avg1 < 60 Then
MSF1.CellForeColor = vbRed
End If
MSF1.TextMatrix(MSF1.Row, g + 1) = avg1
MSF1.Row = MSF1.Row + 1
mrc1.MoveNext
Next i
现在按总分从高到低排序。
数据库连接为Acess,我想,你换一下,没难吧
'设置连接
Set cn = New ADODB.Connection '设置连接
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\result.mdb;Persist Security Info=False "
.Open ' 一定要打开
End With sql = "SELECT 参赛号,总分 FROM T_Table ORDER BY 总分 DESC"'先打开记录集:
Set rs = New ADODB.Recordset '设置rs
rs.Open sql, cn, 1, 1 '*** 一定要打开记录集,
'前面的1为指针按顺序移动,后面的1是值数据库操作限制,1为只读,3为所有人修改 For i = 1 To rs.RecordCount
cn.Execute "UPDATE T_Table SET 名次 =" & i & " WHERE 参赛号='" & rs!参赛号 & "'"
rs.MoveNext
Next i
cn.Close
Set cn = Nothingps:你下次提问时最好说清楚每个字段的中文意思,而不要只是一堆程序