Median value

[bayman]

Is it possible to derive the median value of a field via a query? The absence of any tips in the Help facility suggests not.

 

[jfgambit]

Oh it's out there...

somewhere...

Median

 

[bayman]

Awesome. Thanks!

 

[Lightwave]

Median of filtered subform..

Dear All,

I am putting together a database of triathlon races. There are two tables the header table consisting of the race name and the related table that consists of the name of the entrants and their times.

I followed the above link and have got the median calculation working for a field within a sub form. The field within the subform calculates the median for the whole table - irrespective of the race - whereas I was wanting it to calculate a new median for each race if you see.

Thus Erskine 2005 swim time is 13.34 and Tranent 2005 median is 13.34 when in actual fact the median for all information input so far is 13.34 and the individual medians are different.

Probably quite simple but could do with some pointers...

Many thanks

Mark

 

[Mile-O]

Post your code...

 

[Lightwave]

SJ,

The code is exactly the same as the suggested code listed in the above link.
But here it is again.

I then have a Race form with a sub form in it and put a text field down and put in control source...

=Median("11RAce","Swim Time")

Works a treat except it calculates median of swim time for the whole table irrespective of the race.

This is in fact not wrong but I'm actually wanting it to calculate swim times for the race...

Thanks

M

Option Compare Database
Option Explicit

Function Median(tName As String, fldName As String) As Single
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
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function

 

[FoFa]

It would appear you could substitute a query name for the table name, and have your query say, pull criteria from a form and limit the rows the functions sees?
Just thinking out loud

 

[Lightwave]

Cheers Lone Star ... think that solution will be something along those lines.

M

 

[Lightwave]

Progress of sorts - now works out the median for the race I query but this is fixed - so thinking out loud I think I need to make a variable that changes with the form so that when the form changes the query changes but the code for the text box stays referenced to the same query.

M

 

[Mile-O]

OKay, I've reworked the code a little bit. I've added two new arguments. These are strSeries (String) and lngSeries (Long). Provided you have a properly normalised database this should work. This is assuming you have a table for races with an autonumber ID. strSeries is the name of the ID field and lngSeries is the actual value of the field.

Public Function Median(ByVal strTable As String, ByVal strField As String, _
    ByVal strSeries As String, ByVal lngSeries As Long) As Single
    
    On Error GoTo Err_Median

    Const cID As String = "%ID%"
    Const cField As String = "%FieldName%"
    Const cTable As String = "%TableName%"
    Const cSeries As String = "%Series%"
    Const SQL As String = "SELECT [%FieldName%] FROM [%TableName%] " & _
                          "WHERE [%FieldName%] Is Not Null AND [%Series%] = %ID% " & _
                          "ORDER BY [%FieldName%];"

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim strSQL As String
    Dim OffSet As Integer
    Dim RCount As Integer
    Dim i As Integer
    Dim x As Double
    Dim y As Double
    
    strSQL = Replace(SQL, cField, strField)
    strSQL = Replace(strSQL, cTable, strTable)
    strSQL = Replace(strSQL, cSeries, strSeries)
    strSQL = Replace(strSQL, cID, lngSeries)

    Set db = CurrentDb
    Set rs = MedianDB.OpenRecordset(strSQL)
   
    rs.MoveLast
    RCount = rs.RecordCount
    
    x = RCount Mod 2
    
    If x <> 0 Then
        OffSet = ((RCount + 1) / 2) - 2
        For i% = 0 To OffSet
            rs.MovePrevious
        Next i
        Median = rs(strField)
    Else
        OffSet = (RCount / 2) - 2
        For i = 0 To OffSet
            rs.MovePrevious
        Next i
        x = rs(strField)
        rs.MovePrevious
        y = rs(strField)
        Median = (x + y) / 2
    End If
    
    rs.Close
    db.Close
    
Exit_Median:
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
Err_Median:
    Median = 0
    Resume Exit_Median
    
End Function

 

[Lightwave]

Thanks SJ - I 've just been coming round to the same idea - will iron out the problems and then post when I 'm further down the line.

M

 

[jnixon]

Wait...uh, can't we use SQL to calculate the 50th percentile of a vertical field? Is that the same as the Median?