计算访问查询中6个字段的每个记录的中位数 [英] Calculate median for each record of 6 fields in an access query
问题描述
我正在使用Access 2007.我试图在访问查询中计算每个学生的六个测试分数的中位数。有些学生不会获得所有分数，并且该字段的值将为空。我已经尝试了许多vba函数，但我发现没有正确处理空值。我在查询中输入以下内容作为计算字段：medianscore：MedianY（[PVR]，[PVR]，[ALR]，[ARR]，[FTR]，[PPR]）
我以为会有一种方法在代码中使用isnumeric函数但无法使其工作。我的另一个想法是以某种方式不在计算字段中包含字段为空的字段。我无法弄清楚如何做这项工作。类似Medianscore的东西：MedianY（IIF（Not IsNull（[PVR]，[PVR]，....
查询中的字段：
Student_id
RCR
PVR
ALR
ARR
FTR
PPR
这里是我发现的现有功能之一 - 它适用于那些拥有全部6分的人但我需要以某种方式排除空值包含因为它们仍被视为数组中的元素。
函数MedianY（ParamArray varNums（）As Variant）As Variant
''** *****************************************
' '目的：从参数中返回中位数
''数字数组
''编码：raskew
''输入：（1 ）？medianY（1,11,8,3,6,13）
''（2）？medianY（1,11,8,3,6）
''输出：（1）7
''（2）6
''****************** ****************** *****¥
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim temp As Integer
n = UBound（varNums）
If（n< 0）然后
退出函数
否则
''使用冒泡排序对元素进行排序
''（适用于少量元素，但
''对较大的种类来说很慢）
对于i = 0到UBound（varNums）
对于j = 0到UBound（varNums）
如果varNums（i）< varNums（j）然后
temp = varNums（i）
varNums（i）= varNums（j）
varNums（j）= temp
结束如果
下一个j
下一个我
结束如果
''如果有一个奇数个元素，中位数=中心元素
''例如如果元素= 1,3,6,8,11则中位数= 6
''使用偶数元素，中位数= 2个中心元素的平均值
''例如如果元素= 1,3,6,8,11,13则中位数=（6 + 8）/ 2 = 7
MedianY = IIf（n Mod 2 = 0，varNums（n / 2） ，（varNums（n \ 2）+ varNums（n \ 2 + 1））/ 2）
结束功能
I am using Access 2007. I am trying to calculate in an access query the median of six test scores for each student. Some students will not have all scores and the value of the field would be null. I have tried a number of vba functions but I have found none that handles the nulls correctly. I enter the following as a calculated field in the query: medianscore:MedianY([PVR],[PVR],[ALR],[ARR],[FTR],[PPR])
I thought there would be a way to use the isnumeric function in the code but could not get it to work. Another thought I had was to somehow not include in the calculated field those fields where the field was null. I could not figure out how to make that work either. Something like Medianscore: MedianY(IIF(Not IsNull([PVR],[PVR],....
Fields in the query:
Student_id
RCR
PVR
ALR
ARR
FTR
PPR
Here''s one of the existing functions I have found - it works for those with all 6 scores but I need to somehow exclude the null values from being included because they still get counted as being elements in the array.
Function MedianY(ParamArray varNums() As Variant) As Variant
''*******************************************
''Purpose: Return the median from a parameter
'' array of numbers
''Coded by: raskew
''Inputs: (1) ? medianY(1,11,8,3,6,13)
'' (2) ? medianY(1,11,8,3,6)
''Output: (1) 7
'' (2) 6
''*******************************************
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim temp As Integer
n = UBound(varNums)
If (n < 0) Then
Exit Function
Else
''use bubble sort to sequence the elements
''(good for small number of elements but
''slow for larger sorts)
For i = 0 To UBound(varNums)
For j = 0 To UBound(varNums)
If varNums(i) < varNums(j) Then
temp = varNums(i)
varNums(i) = varNums(j)
varNums(j) = temp
End If
Next j
Next i
End If
''If there''s an odd number of elements, median = center element
''e.g. if elements = 1,3,6,8,11 then median = 6
''With an even number elements, median = average of 2 center elements
''e.g. if elements = 1,3,6,8,11,13 then median = (6+8)/2 = 7
MedianY = IIf(n Mod 2 = 0, varNums(n / 2), (varNums(n \ 2) + varNums(n \ 2 + 1)) / 2)
End Function
推荐答案
您可能想重新考虑表的设计。您应该有三个字段：StudentID，Course，Score。这样，将不会停留空值（并且不会限制为6个课程），并且通过select语句对数据进行排序会更加容易。
You might want to reconsider the design of your table. You should have three field : StudentID, Course, Score. That way, will will not be stuck with null values (and will not be limited to 6 courses), and it will be much easier to sort your data through select statement.
I don'目前没有这个选择。这只是我们报告的一个更大的数据库的1个部分。每个学生都有数百个分数，并且有大量的报告基于一个学生的一种记录格式。
I don''t have that option at present. This is only 1 piece of a much larger data base from which we report from. Each student has hundreds of scores and there are numerous reports based on the one student one record format.
第二个想法，我会尝试这个并且只保存这个区域的最终分数然后我总是可以将它们与其他信息合并。
On second thought, I will try this and just save out the final scores in this one area and then I can always merge them back in with the other info.
这篇关于计算访问查询中6个字段的每个记录的中位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
