Calculat Median for each data group?

N

nacholibre

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?

Thank you!

Paul
 
C

Cinzia

nacholibre said:
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?
Click to expand...
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
 
N

nacholibre

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 said:
nacholibre said:
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?
Click to expand...
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
Click to expand...
 
C

Cinzia

"nacholibre" <[email protected]> ha scritto nel
messaggio
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?
Click to expand...
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;
Click to expand...
Click to expand...

Hi nacholibre
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?
Click to expand...

Peraphs there is an error in the MedianF Function, check if it does the
correct work outside the query,
for example if REGION is a Text field the strSQL should be:
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0
AND REGION = '" & region & "' Order by " & pfield & ";"
To check in Immediate Window type ? medianF("MARKET_RATES","RATE", "ALASKA")

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]?
Click to expand...

yes, if you want the median rate for every REGION, BIN1, BIN2, BIN3 you
have to pass all the value to the function and change the strSQL accordingly


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

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

my VBA code to calculate median values is broken. :( 3
Problem with Median Calculation / VBA Script 2
median function problem 7
Median calculation 2
Calculating median in a group by query 4
median function on parameter query 6
Calculation for Median 3
Median Calculation, Dates, & Group By 1

Top