查询综合应用
根据要求去掉最大与最小的数据，然后求平均分
2008-12-08 08:56:23

关于平均值的问题,如何根据被考核人和考项这两个条件求各项成绩的平均值当每个人的考项的个数大于0，求平均，大于19，求减去一个最大和最小后的平均值，大于39，求减去2个最大和最小后的平均值，大于59，求减去3个最大和最小后的平均值，大于79，求减去4个最大和最小后的平均值，请帮忙，谢谢

答：

根据要求去掉最大与最小的数据，然后求平均分

这是求得各项批标的总分后求得平均分

如要筛除各项指标的最大值与最小值，可以看示例2259[vd].rar

示例下载:点击这里下载

Sub aExcuteEvents() Dim rsyg As New ADODB.Recordset rsyg.Open "qrykxcount", CurrentProject.Connection, 1, 2 Do While Not rsyg.EOF goFindrec rsyg("被考核人员"), rsyg("考项") rsyg.MoveNext Loop

End Sub

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

sql = "select id from qryScoreOrder where " & strCri & " order by 总得分" Debug.Print sql rs.Open sql, 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 "tblcal", CurrentProject.Connection, 1, 2 rs.Move j For i = 1 To intKxCount - j * 2

    rsCal.AddNew     rsCal("id") = rs("ID")     Debug.Print "添加到tblcal表的记录:" & rs.AbsolutePosition, rs("id")     rsCal.Update     rs.MoveNext Next i End Sub

首页