G

#### Guest

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?

Thank you!

Paul