Calculation for Median

D

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
 
T

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
 
J

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.
 
J

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.

Tom van Stiphout said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

median 31
YES 0
Calculating the Median for specified groups 1
Calculating median in a group by query 4
Median in Report 1
Median in Report 1
Please help 6
Statistical Median Code 3

Top