434,871 Members | 2,595 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 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
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 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 Expand|Select|Wrap|Line Numbers 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 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("", "")   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? Jan 16 '12 #11

 100+ P: 332 Line 7 : Expand|Select|Wrap|Line Numbers Dim RCount As Integer Replace with Expand|Select|Wrap|Line Numbers 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 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 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