Another Median Question... (1 Viewer)

[jcktm]

Newto Access, new to the board. Have searched for median queries and have not found what I am looking for.

I want to incorporate a MEDIAN calculation into a querey. I have 6400+ records, which I need to be grouped by 61 industries. I have suceessuflly grouped and got the MAX, MIN, AVG, etc... and I would now like to add into this grouped query the MEDIAN calculation. Can someone here help a brother out?

 

[raskew]

Hi-

Here are a couple of functions that will return the median. Try testing the first one--MedianF()-- against Northwind's Orders table.

HTH - Bob

Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight") <enter.
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

Function Medianx(ParamArray varNums() As Variant) As Variant
'*******************************************
'Purpose:   Return the median from a parameter
'           array of numbers
'Coded by:  raskew
'Inputs:    (1) ? medianx(1,11,8,3,6,13)
'           (2) ? medianx(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
    Medianx = IIf(n Mod 2 = 0, varNums(n / 2), (varNums(n \ 2) + varNums(n \ 2 + 1)) / 2)
    
    'To display results, uncomment the following 3 lines
    'For i = 0 To UBound(varNums)
    '   Debug.Print varNums(i)
    'Next i

End Function

 

[jcktm]

Still looking for some help

Maybe this picture will help explain what I am looking to do. The last reply did not work for me.... I want the median of OBS60

 

[nateobot]

Why did the last reply not work for you? Was it because you did not understand it, or because it failed to run? It worked real well in my test.

Where you have the Median placed in your pdf is the criteria row.

 

[jcktm]

Did not run

I think I understand it.. It did not work in my mdb. I dont have access to the northwind mdb here at work, but used the syntax in my database. I created a module, saved it, used the syntax in the top line of the screen shot I saved... got the error: I have to go check the error....

 

[jcktm]

here is the error

I think I defineteley need to learn more about programing queries and such... how far off base am I here?

 

[nateobot]

Actually you are not far from what is needed at all.

Have it look like so:

Expr1: MedianF("[NorthAm R2]","[OBS60]")