Calculate median for each record of 6 fields in an access query

[brentg]

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:Med ianY([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(ParamAr ray 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

[Mariostg]

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.

[brentg]

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.

[brentg]

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.

[Mariostg]

It is always good to think about it twice... :)
If you say each students have hundreds of scores, do you mean you have hundreds of columns? That is the way I interpret your situation.

[brentg]

Well I guess not hundreds it just seems like it. 106 scores in the original raw score table and a number of demographic fields for each student in aother table. The raw score tables are used in some detailed reports and then these final cummulative scores are used in some other reports.

[Mariostg]

I assume then that one student has only one row, with several columns representing the courses. In that case then, it might be simpler to bring that table into Excel, and calculate the median from there.

[brentg]

Probably not feasible as the table will be repeatedly updated as we get changes and report throughout the year. If I get the data into the other format I should be able to use some of the median "group by" code I have seen on the web.

[brentg]

I am having troubles getting any of the existing median functions I have found to do what I want. I would need the function to work in a query so that I can save out the value and provide a median score for each student_id. I now have the data like this: Up to six types of tests per student. I have removed any null results. Scores range from 1-21. Any suggestions as to which code would work for this?
Student_id Test Score
90012345 RCR 3
90012345 PVR 4
...
90045678 RCR 13
90045678 FTR 14
90045678 PPR 13

What I want:
Student_id Median_score
90012345 3.5
90045678 13

Thanks.

[Mariostg]

Brentg, have to tried this How to use code to derive a statistical median

[brentg]

Yes, I have but when I try to use it in a query :

SELECT math_medianstep2.student_id, mmedian("math_medianstep2","score") AS med
FROM math_medianstep2
GROUP BY math_medianstep2.student_id;

I get "run time error 6 Overflow" and the debug takes me to this section of the code

RCount% = ssMedian.RecordCount

the cursor shows the value of Rcount% is 0 nd RecordCount is 97729 (the number of records in the table).
Here's my code, I am using MMedian because I have a version of median elsewhere in my database:

Function MMedian(tName As String, fldName As String) As Single 'from microsoft support
'Article ID: 210581 - Last Review: December 12, 2007 - Revision: 4.0
'How to use code to derive a statistical median
'=MMedian("<TableName>", "<FieldName>")
  Dim MedianDB As DAO.Database
  Dim ssMedian As DAO.Recordset
  Dim RCount As Integer, i As Integer, x As Double, y As Double, _
      OffSet As Integer
  Set MedianDB = CurrentDb()
  Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];")
  'NOTE: To include nulls when calculating the median value, omit
  'WHERE [" & fldName & "] IS NOT NULL from the example.
  ssMedian.MoveLast
  RCount% = ssMedian.RecordCount
  x = RCount Mod 2
  If x <> 0 Then
     OffSet = ((RCount + 1) / 2) - 2
     For i% = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     MMedian = ssMedian(fldName)
  Else
     OffSet = (RCount / 2) - 2
     For i = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     x = ssMedian(fldName)
     ssMedian.MovePrevious
     y = ssMedian(fldName)
     MMedian = (x + y) / 2
  End If
  If Not ssMedian Is Nothing Then
     ssMedian.Close
     Set ssMedian = Nothing
  End If
  
  Set MedianDB = Nothing
End Function

Can this code be run in a group by query?

[Mariostg]

Line 7 :

Dim RCount As Integer

Replace with

Dim RCount As Long

with Integer, range value is -32,768 to 32,767

[brentg]

It did not like this part

RCount% = ssMedian.RecordCount

Error message: Type-declaration character does not match declared data type. If I take the % off it then says:run time error 6 and pauses here:

OffSet = ((RCount + 1) / 2) - 2

where the cursor shows offset =0.

[Mariostg]

The % sign has nothing to do there. What will be the largest possible expected value of Offset? Hint, larger than what's expected from an Integer?