Re: Calculat Median for each data group?

Hello Cinzia,

And thank you very much for the help. I made the changes you suggested, and
when I run the query I get the following error:

"compile error. in query expression
'medianF("MARKET_RATES","RATE",[REGION])'"

Any thoughts?

And just to be clear - I'm looking for a different median rate to be
calculated for every REGION, BIN1,BIN2,BIN3 combination. Would I need to add
those additional elements to the code - example [REGION], [BIN1], [BIN2],
[BIN3]?

Thank you again for all of your help!! :)

"Cinzia" wrote:




"nacholibre" <nacholibre@xxxxxxxxxxxxxxxxxxxxxxxxx> ha scritto nel messaggio
news:DC69CC69-6DDF-4D16-94DD-C735908E3157@xxxxxxxxxxxxxxxx
I have a table with the folling fields:

REGION COMPETITOR BIN1 BIN2 BIN3 PRICE

and I am using this function to calculate a median:

Function MedianF(pTable As String, pfield As String) As Single

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
-----------------------------------
So the full query looks like this:

SELECT REGION, BIN1, BIN2, BIN3, medianF("MARKET_PRICES","PRICE") AS
mymedian

FROM MARKET_PRICES

GROUP REGION, BIN1, BIN2, BIN3;

When I run it, the query returns the same median value for every row. I
believe it is calculating a single median using all of the prices in the
table.

How can I alter the query to reference the function properly and calculate
the correct median for every group?


Hi Paul,
the query is correct, you have to change the function to obtain the result
expected.
So if you want the median Price for each Region the function will be:

Function MedianF(pTable As String, pfield As String, region as string) As
Single

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

strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield &
">0 A ND REGION = " & region & " 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

and the query will be:
SELECT REGION, BIN1, BIN2, BIN3, medianF("MARKET_PRICES","PRICE", [REGION])
AS mymedian
FROM MARKET_PRICES
GROUP BY REGION, BIN1, BIN2, BIN3;

Bye

--
Cinzia [Office Access MVP]
_______________________
www.riolab.org
----------------------------------------



.

Relevant Pages

  • Re: Calculat Median for each data group?
    ... Function MedianF(pTable As String, ... Dim strSQL As String ... Dim sglHold As Single ... the query returns the same median value for every row. ...
    (microsoft.public.access.queries)
  • Re: Calculat Median for each data group?
    ... Function MedianF(pTable As String, ... Dim strSQL As String ... Dim sglHold As Single ... the query returns the same median value for every row. ...
    (microsoft.public.access.queries)
  • Median Calculation, Dates, & Group By
    ... I want to calculate the medians for a string of values that are going to be ... I have tried the median modules that were posted by John Nurick, ... values as this in the same query). ... Dim rsMedian As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • RE: group and median
    ... Lets assume that you want to run a query on "yourTable", ... "SomeField" and compute the median on the field ... Public Function fnMedian(FieldName As String, Source As String, _ ... fnMedian = rs ...
    (microsoft.public.access.queries)
  • Re: How to obtain a median?
    ... Function MedianF(pTable As String, ... Return median value from a recordset (uses Northwind's Orders ... Dim strSQL As String ... Dim sglHold As Single ...
    (microsoft.public.access.queries)
Loading