Calculation for Median

[djtoney4464]

Please help me understand how to get this to work. I opened a new module and
named it modMedianCalc. Here is my SQL



Public Function MedianCalc(tbl_EstVsActual As String, Median 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 [" & Median & _

"] FROM [" & tbl_EstVsActual & "] WHERE [" & Median & _

"] IS NOT NULL ORDER BY [" & Median & "];")

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(Median)

Else

OffSet = (RCount / 2) - 2

For i = 0 To OffSet

ssMedian.MovePrevious

Next i

x = ssMedian(Median)

ssMedian.MovePrevious

y = ssMedian(Median)

Median = (x + y) / 2

End If

If Not ssMedian Is Nothing Then

ssMedian.Close

Set ssMedian = Nothing

End If

Set MedianDB = Nothing

End Function

 

[Tom van Stiphout]

On Wed, 5 May 2010 06:29:01 -0700, djtoney4464

This function "works" all by itself. What is not working about it?
Call it with a tablename and a field name, and it will return the
median. Or so it seems. This code is so very inelegant and inefficient
I almost choked on my breakfast.

-Tom.
Microsoft Access MVP

 

[Jeff Boyce]

If you're not satisfied with this function, have you looked into referencing
Excel and using its Median() function?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

Tom

Maybe this code would qualify for an award ... sort of like the IgNobles of
coding...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

On Wed, 5 May 2010 06:29:01 -0700, djtoney4464

This function "works" all by itself. What is not working about it?
Call it with a tablename and a field name, and it will return the
median. Or so it seems. This code is so very inelegant and inefficient
I almost choked on my breakfast.

-Tom.
Microsoft Access MVP

Please help me understand how to get this to work. I opened a new module
and
named it modMedianCalc. Here is my SQL



Public Function MedianCalc(tbl_EstVsActual As String, Median 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 [" & Median & _

"] FROM [" & tbl_EstVsActual & "] WHERE [" & Median & _

"] IS NOT NULL ORDER BY [" & Median & "];")

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(Median)

Else

OffSet = (RCount / 2) - 2

For i = 0 To OffSet

ssMedian.MovePrevious

Next i

x = ssMedian(Median)

ssMedian.MovePrevious

y = ssMedian(Median)

Median = (x + y) / 2

End If

If Not ssMedian Is Nothing Then

ssMedian.Close

Set ssMedian = Nothing

End If

Set MedianDB = Nothing

End Function