By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

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

P: 9
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
Jan 11 '12 #1
Share this Question
Share on Google+
13 Replies


100+
P: 332
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.
Jan 12 '12 #2

P: 9
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.
Jan 12 '12 #3

P: 9
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.
Jan 12 '12 #4

100+
P: 332
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.
Jan 12 '12 #5

P: 9
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.
Jan 12 '12 #6

100+
P: 332
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.
Jan 12 '12 #7

P: 9
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.
Jan 12 '12 #8

P: 9
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.
Jan 13 '12 #9

100+
P: 332
Brentg, have to tried this How to use code to derive a statistical median
Jan 16 '12 #10

P: 9
Yes, I have but when I try to use it in a query :
Expand|Select|Wrap|Line Numbers
  1. SELECT math_medianstep2.student_id, mmedian("math_medianstep2","score") AS med
  2. FROM math_medianstep2
  3. GROUP BY math_medianstep2.student_id;
  4.  
I get "run time error 6 Overflow" and the debug takes me to this section of the code
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Function MMedian(tName As String, fldName As String) As Single 'from microsoft support
  2. 'Article ID: 210581 - Last Review: December 12, 2007 - Revision: 4.0
  3. 'How to use code to derive a statistical median
  4. '=MMedian("<TableName>", "<FieldName>")
  5.   Dim MedianDB As DAO.Database
  6.   Dim ssMedian As DAO.Recordset
  7.   Dim RCount As Integer, i As Integer, x As Double, y As Double, _
  8.       OffSet As Integer
  9.   Set MedianDB = CurrentDb()
  10.   Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
  11.             "] FROM [" & tName & "] WHERE [" & fldName & _
  12.             "] IS NOT NULL ORDER BY [" & fldName & "];")
  13.   'NOTE: To include nulls when calculating the median value, omit
  14.   'WHERE [" & fldName & "] IS NOT NULL from the example.
  15.   ssMedian.MoveLast
  16.   RCount% = ssMedian.RecordCount
  17.   x = RCount Mod 2
  18.   If x <> 0 Then
  19.      OffSet = ((RCount + 1) / 2) - 2
  20.      For i% = 0 To OffSet
  21.         ssMedian.MovePrevious
  22.      Next i
  23.      MMedian = ssMedian(fldName)
  24.   Else
  25.      OffSet = (RCount / 2) - 2
  26.      For i = 0 To OffSet
  27.         ssMedian.MovePrevious
  28.      Next i
  29.      x = ssMedian(fldName)
  30.      ssMedian.MovePrevious
  31.      y = ssMedian(fldName)
  32.      MMedian = (x + y) / 2
  33.   End If
  34.   If Not ssMedian Is Nothing Then
  35.      ssMedian.Close
  36.      Set ssMedian = Nothing
  37.   End If
  38.  
  39.   Set MedianDB = Nothing
  40. End Function
  41.  
Can this code be run in a group by query?
Jan 16 '12 #11

100+
P: 332
Line 7 :
Expand|Select|Wrap|Line Numbers
  1. Dim RCount As Integer
Replace with
Expand|Select|Wrap|Line Numbers
  1. Dim RCount As Long
with Integer, range value is -32,768 to 32,767
Jan 16 '12 #12

P: 9
It did not like this part
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. OffSet = ((RCount + 1) / 2) - 2
where the cursor shows offset =0.
Jan 16 '12 #13

100+
P: 332
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?
Jan 16 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.