Median 2

[FoxProProgrammer]

Is there a function in Visual Basic or Access to calculate the Median of a set of data? Do I need to include a library Reference to get it? It seems like there should be a Median function in Access, but I can't find it.

Thanks,

dz

 

[MichaelRed]

Personally, median is a bit to simplistic to actually require an intrinsic function, so9 I have never even looked for one. If you have limited sets of values (30 or fewer values in a given set) and really want to go to the extreme, you could add a ref to the Excel library and use the function included therein. If you want MORE than this, you might loook up the Excel function and just program the function for yourself in VBA.





MichaelRed

 

[FoxProProgrammer]

Thanks for your reply, MichaelRed. There could be 1000s of values in a given set. I understand how to write my own Median function (basically sort the data, count the number of data points. Divide by two and get that value from the set). However, I didn't want to go to the trouble of writing a function if one already existed in Access. They have Max, Min, Sum, Average, etc., which in my opinion are more trivial than calculating the Mean. If I were to add a reference to the Excel function, how would I go about it? I haven't ever tried that, and it will be good to know how to do it.

Thanks for your help.



dz
dzaccess@yahoo.com

 

[MichaelRed]

per my first post, Excel is limited to a set of thirty values, so is 'nigh-unto-useless' for you host of hordes issue ('1000s') so it is back to the 'independent development'.

Given that you are realy doing the K's of elements, I would suggest the use of some sort of recordset to do at least a bit of the porcess. I did recall doing a somewhat crude version for something a while back, however be AWARE that it does not include any of the real 'nicieties' such as documentation. error handling, ...:

Public Function basMedian(MyField As String, _
                          MyTable As String, _
                          Optional MyRel As String = "", _
                          Optional MyCriteria As String = "", _
                          Optional Delim As String = "") As Double

    Dim Sql As String
    Dim rst As DAO.Recordset
    Dim intMiddle As Integer
    Dim NumToCalc As Integer
    Dim valMedian As Double

    Sql = "Select " & MyField & " From " & MyTable
    If MyCriteria <> "" Then
       Sql = Sql & " Where " & " " & MyField & " "
       Sql = Sql & MyRel & " " & Chr(34) & MyCriteria & Chr(34)
    End If
    Sql = Sql & " Order By " & MyField

    Set rst = CurrentDb.OpenRecordset(Sql, dbOpenDynaset)
    If (rst.EOF = True And rst.BOF = True) Then
        basMedian = 0
     Else
        rst.MoveLast
        rst.MoveFirst

        For intMiddle = 1 To rst.RecordCount \ 2
           rst.MoveNext
        Next intMiddle

        NumToCalc = (rst.RecordCount \ 2) Mod 2
        For intMiddle = 1 To NumToCalc
            valMedian = rst.Fields(MyField) + basvalMedianMedian
        Next intMiddle
        basMedian = valMedian / (NumToCalc - 1)

    End If

End Function

MichaelRed

 

[FoxProProgrammer]

Thank you very much. I created my own function before I read your message, unfortunately. We are basically doing the same thing. The problem now is that Access doesn't recognize my function when I use it in a Query. The error is "Undefined function "Median" in expression." I must admit that I am a little rusty on Access because I have been doing other things for more than a year. Thanks so much for your help.

Here is my Query:

SELECT Median("TestData", "TestTbl") FROM TestTbl;

Here is my function.

Function Median(fldname As String, tblname As String)

Dim strQuery As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim numRecs, dataPt As Integer

' Get the data and sort it.
strQuery = "Select " & fldname & " From " & tblname & " Order by " & fldname
Set db = CurrentDb
Set rs = db.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)

' Count the number of records and move to the beginning of record set.
numRecs = rs.RecordCount
rs.MoveFirst

' Find the Median
dataPt = Int((numRecs + 1) / 2)

If dataPt Mod 2 = 0 Then
    ' Odd number of records.
    rs.Move (dataPt - 1)
    Median = rs.testdata
Else
    ' Even number of records.  Average the middle two values.
    rs.Move (dataPt - 1)
    Median = rs.testdata
    rs.MoveNext
    Median = (Median + rs.testdata) / 2
End If

End Function

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

By the way, I realize that I need to change rs.testdata to rs.Fields(fldname) to generalize the function. I wrote it with a specific field name just to get it to work. Thanks again!

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Hey, MichaelRed:

I figured out why my function wasn't working in the Query. The name of my database was the same as the name of my function, and I guess it conflicted. I renamed the database (mdb) file to something else, and the Query works. Thanks again for your help.



dz
dzaccess@yahoo.com

 

[MichaelRed]

In some instances, Ms. A. does not have the full record count unless the recordset has been traveresed, so I would suggest making sure that your recordset is forced to do the traversal before that assignment.





MichaelRed

 

[FoxProProgrammer]

Thanks for the reminder! I made that correction, among others. Here is the corrected code.

Public Function Median(fldname As String, tblname As String) As Single

Dim strQuery As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim numRecs, dataPt As Integer

' Get the data and sort it.
' strQuery = "Select [" & fldname & "] From [" & tblname & "] Where NOT IsNull([" & fldname & "]) Order by [" & fldname & "];"
strQuery = "Select * From [" & tblname & "];"
Set db = CurrentDb
Set rs = db.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)

' Count the number of records and move to the beginning of record set.
rs.MoveLast
numRecs = rs.RecordCount
rs.MoveFirst

dataPt = Int((numRecs + 1) / 2)

' Find the Median
If dataPt Mod 2 = 0 Then
    ' Even number of records.  Average the middle two values.
    rs.Move dataPt - 1
    Median = rs.Fields(fldname)
    rs.MoveNext
    Median = (Median + rs.Fields(fldname)) / 2
Else
    ' Odd number of records.
    rs.Move dataPt - 1
    Median = rs.Fields(fldname)
End If

End Function

dz
dzaccess@yahoo.com