关于平均值的问题

Victor_Duane

建一个新表tblcalfliter，然后运行aexcuteevents,我前面给你的查询不要删除，

然后你自己做一个查询，得出这个表里的平均值
Sub aExcuteEvents()
CurrentProject.Connection.Execute "delete * from tblcalfliter"
Dim rsyg As New ADODB.Recordset
rsyg.Open "qrykxcount", CurrentProject.Connection, 1, 2
Do While Not rsyg.EOF
goFindrec rsyg("被考核人员"), rsyg("考项"), "中层", "tblCalFliter"
rsyg.MoveNext
Loop

End Sub

Sub goFindrec(strXm As String, strKx As String, TblName As String, AddSql As String)

Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset
Dim rs5 As New ADODB.Recordset
Dim rs6 As New ADODB.Recordset
Dim rs7 As New ADODB.Recordset
Dim rs8 As New ADODB.Recordset
Dim rs9 As New ADODB.Recordset


Dim fld(9) As String
Dim sql(9) As String
fld(0) = "工作责任心"
fld(1) = "敬业精神"
fld(2) = "执行力度"
fld(3) = "积极主动"
fld(4) = "办事公道"
fld(5) = "廉洁自律"
fld(6) = "创新精神"
fld(7) = "全局观念"
fld(8) = "团结协作意识"
fld(9) = "工作能力及方法"

Dim strCri As String
strCri = "[被考核人员]='" & strXm & "' and [考项]='" & strKx & "'"
Debug.Print strCri
Dim intKxCount As Integer
intKxCount = DCount("考项", "中层", strCri)
Debug.Print "被考核人员的考项数:" & intKxCount



sql(0) = "select " & fld(0) & " from " & TblName & " where " & strCri & " order by " & fld(0)
sql(1) = "select " & fld(1) & " from " & TblName & " where " & strCri & " order by " & fld(1)
sql(2) = "select " & fld(2) & " from " & TblName & " where " & strCri & " order by " & fld(2)
sql(3) = "select " & fld(3) & " from " & TblName & " where " & strCri & " order by " & fld(3)
sql(4) = "select " & fld(4) & " from " & TblName & " where " & strCri & " order by " & fld(4)
sql(5) = "select " & fld(5) & " from " & TblName & " where " & strCri & " order by " & fld(5)
sql(6) = "select " & fld(6) & " from " & TblName & " where " & strCri & " order by " & fld(6)
sql(7) = "select " & fld(7) & " from " & TblName & " where " & strCri & " order by " & fld(7)
sql(8) = "select " & fld(8) & " from " & TblName & " where " & strCri & " order by " & fld(8)
sql(9) = "select " & fld(9) & " from " & TblName & " where " & strCri & " order by " & fld(9)



rs.Open sql(0), CurrentProject.Connection, 1, 2
rs1.Open sql(1), CurrentProject.Connection, 1, 2
rs2.Open sql(2), CurrentProject.Connection, 1, 2
rs3.Open sql(3), CurrentProject.Connection, 1, 2
rs4.Open sql(4), CurrentProject.Connection, 1, 2
rs5.Open sql(5), CurrentProject.Connection, 1, 2
rs6.Open sql(6), CurrentProject.Connection, 1, 2
rs7.Open sql(7), CurrentProject.Connection, 1, 2
rs8.Open sql(8), CurrentProject.Connection, 1, 2
rs9.Open sql(9), CurrentProject.Connection, 1, 2



Debug.Print "记录集的记录数:" & rs.RecordCount

    Dim i As Integer
    Dim j As Integer

    Select Case intKxCount
        Case Is <= 19
        j = 0
        Case Is <= 39
        j = 1
        Case Is <= 59
        j = 2
        Case Is <= 79
        j = 3
        Case Is > 79
        j = 4
    End Select
    Debug.Print "筛除记录数:" & j & "×2"

Dim rsCal As New ADODB.Recordset



rsCal.Open AddSql, CurrentProject.Connection, 1, 2

rs.Move j
rs1.Move j
rs2.Move j
rs3.Move j
rs4.Move j
rs5.Move j
rs6.Move j
rs7.Move j
rs8.Move j
rs9.Move j


For i = 1 To intKxCount - j * 2

    rsCal.AddNew
    rsCal("被考核人员") = strXm
    rsCal("考项") = strKx
    rsCal(fld(0)) = rs(fld(0))
    rsCal(fld(1)) = rs1(fld(1))
    rsCal(fld(2)) = rs2(fld(2))
    rsCal(fld(3)) = rs3(fld(3))
    rsCal(fld(4)) = rs4(fld(4))
    rsCal(fld(5)) = rs5(fld(5))
    rsCal(fld(6)) = rs6(fld(6))
    rsCal(fld(7)) = rs7(fld(7))
    rsCal(fld(8)) = rs8(fld(8))
    rsCal(fld(9)) = rs9(fld(9))
    rsCal.Update
   
    rs.MoveNext
    rs1.MoveNext
    rs2.MoveNext
    rs3.MoveNext
    rs4.MoveNext
    rs5.MoveNext
    rs6.MoveNext
    rs7.MoveNext
    rs8.MoveNext
    rs9.MoveNext
      
Next i
End Sub

Victor_Duane

传上示例看是明白一点，是不是这样的

Victor_Duane

如果你写了ID字段的话，这里更新一下，加上一句
    rsCal("id") = rs.AbsolutePosition
这样就知道一批记录是否有筛除，如果没有筛除的话是从1开始
如果筛除了就会从开始的记录数写入，看的时候也可以大概知道
这批记录被除去了多少记录
当然在立即窗体中，也可以看到各批数据的信息

For i = 1 To intKxCount - j * 2

    rsCal.AddNew
    rsCal("id") = rs.AbsolutePosition
    rsCal("被考核人员") = strXm
    rsCal("考项") = strKx
    rsCal(fld(0)) = rs(fld(0))
    rsCal(fld(1)) = rs1(fld(1))
    rsCal(fld(2)) = rs2(fld(2))
    rsCal(fld(3)) = rs3(fld(3))
    rsCal(fld(4)) = rs4(fld(4))
    rsCal(fld(5)) = rs5(fld(5))
    rsCal(fld(6)) = rs6(fld(6))
    rsCal(fld(7)) = rs7(fld(7))
    rsCal(fld(8)) = rs8(fld(8))
    rsCal(fld(9)) = rs9(fld(9))
    rsCal.Update
   
    rs.MoveNext
    rs1.MoveNext
    rs2.MoveNext
    rs3.MoveNext
    rs4.MoveNext
    rs5.MoveNext
    rs6.MoveNext
    rs7.MoveNext
    rs8.MoveNext
    rs9.MoveNext
      
Next i
End Sub

[ 本帖最后由 Victor_Duane 于 2007-12-3 22:37 编辑 ]

lhsh

原帖由 cuxun 于 2007-12-3 20:17 发表
说实话,你讲得不是很清楚我做了个例子你看看

你好！我是新手，怎样操作，请指教

lhsh

是这个结果，但是是不是每一项都减去最大值和最小值，也有可能它们不再一行

Victor_Duane

这是整理过的，打开窗体1运行那个按钮就可以得到你需要的，我都是每项按你的要求来取舍数据的

Victor_Duane

每一次运行都会先删除临时表tblcalfliter表内的数据
然后再进行取按要求筛选的值

lhsh

每个人每类考项中每项（比如工作责任心、敬业精神等）减去最大数和最小数后取平均值，比如说在第二行工作责任心最大，但是敬业精神却是最小，咋办，请指教

Victor_Duane

我的示例已经帮你解决了啊
最后一个示例，你看一下
就是按你的要求做的，不过顺序被我调整了一下，我想不会影响的吧

lhsh

谢谢,   很好