Calculat Median for each data group?

```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

```
 0
Utf
4/5/2007 5:36:05 PM
access.queries 6343 articles. 1 followers.

3 Replies
992 Views

Similar Articles

[PageSpeed] 10

```

"nacholibre" <nacholibre@discussions.microsoft.com> ha scritto nel messaggio
news:DC69CC69-6DDF-4D16-94DD-C735908E3157@microsoft.com...
>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
----------------------------------------

```
 0
Cinzia
4/5/2007 9:35:45 PM
```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@discussions.microsoft.com> ha scritto nel messaggio
> news:DC69CC69-6DDF-4D16-94DD-C735908E3157@microsoft.com...
> >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
> ----------------------------------------
>
>
>
```
 0
Utf
4/11/2007 9:56:09 PM
```>>
>> "nacholibre" <nacholibre@discussions.microsoft.com> ha scritto nel
>> messaggio
>> news:DC69CC69-6DDF-4D16-94DD-C735908E3157@microsoft.com...
>> >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;

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?

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
> those additional elements to the code - example [REGION], [BIN1], [BIN2],
> [BIN3]?

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

```
 0
Cinzia
4/12/2007 8:40:43 PM

Similar Artilces:

How do i add a data label for my y-axis on a line graph created in word? I presume the Word chart has been created with MS Chart? If so Double-click to activate the edit mode for the chart, then choose Chart, Chart Options, Data Labels.. If the chart is an Excel Object then you use the same steps. -- Cheers, Shane Devenshire "dragonfly" wrote: > How do i add a data label for my y-axis on a line graph created in word? ...

Data Validation using List (But needs unique list in drop down lis
Hi all, In sheet 1, column A is my title name while column B is person name. Sheet 1 is my database where i do data entry in this. In sheet 2, contains my query page. In cell A5, i uses data validation - list, on this cell. Say in sheet 1 : column A column B XXXXXXX Mr A YYYYYYYY Mr A ZZZZZZZZ Mr A AAAAAAA Mr B WWWWW Mr C DDDDDDD Mr C But In sheet 2, cell A5, I saw in the drop down list as follows: Mr A Mr A Mr A Mr B Mr C Mr C But i want to see this in cell A5 instead (Unique name that is) : Mr A Mr B Mr C ...

Overwriting data
I have a macro set up to submit data from an entry point on one worksheet into a database on the other. Basically, I enter the info on the first worksheet, hit submit, and it transfers it to the second worksheet. My question is, how do i make it skip to the next line on the second worksheet so that it logs all the submits rather then overwriting the previous? Thanks! -- na This will get you to the cell just below the last populated cell of column A: Application.Goto Reference:="R65536C1" Selection.End(xlUp).Select Selection.Offset(1, 0).Select (You could get this row into a ...

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

Skip blank or N/A in data analysis
Hi, I try to do data analysis (such as regression) to two data columns X ~ Y. Under some condition, some of the Y values are blank or (#N/A). When I do X-Y chart, those points are ignored. However, when I select the same columns to do regress or any other data analysis, I got the error saying "Input range containing non-numeric data". I don't want to put any number like 0 for those to distort my result. I just want to ignore or skip those N/A data. Is there a way I can do it without removing those data? Because the N/A location depends on a variable (a condition). I ...

Migrating data from ACT to CRM (how to get at act! data)
I intend on using the DMF to migrate data to CRM. The question I have is how do I get at the underlying database that ACT uses. I see the MS excel file for ACT has table and field mappings but I need to do some specialized SQL. I can't seem to figure out though how to get into the SQL database where the ACT information is stored. How do I log into that database? Thanks! -Elie From my days working with ACT, I thought the databse was built around Microsoft Access. If indeed the databas is SQL then you should be able to get your SQL DBA to grant you access to the SQL Database. -- Rgds...

My Data Analysis Plus add-in has disappeared. I had it installed at one time, but now it is gone. I've attempted to reinstall the add-in several times and rebooted each time, but to no avail. Running XP Pro and Office Pro software. Any ideas? Thanks, MJ You probably have done this but .... have you looked at Tools/Addins . there is a Browse button in case Excel cannot find it ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

How do you replace old data with new data without creating a new .
I have existing pivot tables and I want to replace the data source worksheet with new data and the pivot tables update with the new information. I have replace the data source but the pivot tables didn't update. Hit the "Refresh Data" in the pivot table toolbar (looks like an exclamation point) -- Regards, Dave "Dena" wrote: > I have existing pivot tables and I want to replace the data source worksheet > with new data and the pivot tables update with the new information. I have > replace the data source but the pivot tables didn't update. So...

Move Data From Vertical to Horizontal
How do I get my speadsheet to move from vertical meaning: Name Type Hours Bob Regular 80.00 Bob OT 10.00 To look like this: Name Regular OT Bob 80.00 10.00 If anyone can help PLEASE comment. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You can create a pivot table to summarize the data. There are some instructions and links here: http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm For ...

publisher: 4 Millions Domains data with Category
Successfull Internet and Direct Marketing products on www.promotionsite.net * NEW * DOMUS Domains Toolkit Fall 2004 - Unique on the Net 4 Millions "Whois" Domains data with Expiration Date and Category*. Ultimate Version (October 2004) - Our best rate starts from US *\$149*. A wonderful tool for Internet and Direct Marketing. Available in Basic, Advanced and Full Editions. It contains a domain database with 4 millions *FRESH* October 2004 New records .com, .net, .org. 4 Gigabytes MS Excel data zipped on CD-Roms/Dvd. Compression 3:1. MS Excel or Text tab del...

Exporting global data from a DLL to an EXE
Hi, I have a "Win32 Application" and I must use some global data defined(and initialized) in a DLL linked to the Application. As suggested by MSDN,I have exported this global data from the DLL in this way: - In the DLL, I have used the keyword DLLEXPORT - In the EXE I have used the keyword DLLIMPORT But when I run the EXE the application crashes! The problem is that, in the EXE, the imported global data are all equal to zero and the application doesn't see the initializationsdone in the DLL! Does someone know where is the problem? Thanks to All. M Micheles, I don&#...

How do you import data from one Excel document to another?
Hey everyone, I work at a school and I'm trying to update their attendance reports from hand written to excel, but I'm running into several problems. For one, I need it to be able to calculate how many "Ab"s (absents) there are in the worksheet (for the individual student's attendance page). And THEN, I need to have the sum show up on a completely new document (for the overall attendance report). Is that possible? I've fooled around with data importing and several other things, but I'm just too new at this. If what I'm asking is possible, can anyone he...

Copy & Paste Data from Web into Excel
When I do the above & use Vlookup for numerical data the numeric data is not recognised, although some data are in another excel workbook. How can I sort the data from the web so that I can do a proper vlookup What looks like a numeric field is actually text. This trick often works: In an empty cell enter the value 1 and copy it Select the offending numbers and use Edit/Paste Special->multiply Delete the cell with the 1 Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Kelvin Pakaree" <k.pakaree@credcor.co.za> wrote in mes...

data labels disappear from graph when i close the worksheet
I have added datalabels to a bar graph. But they go away when i close the worksheet despite having saved the changes. Could any one elt me know why this is happening? Thanks ...

addressing envelope from data input form
I don't know where to start to try this. I have the following fields that make up the entire address: Fname Lname Fname2 Lname 2 Address City, State Zip Is there any way to "push a button" on a form and have it print an envelope for that record? Thanks in advance for any help. -- Posted via a free Usenet account from http://www.teranews.com Have you thought of using an MS template as a starting point? http://office.microsoft.com/en-us/templates/CT101172481033.aspx Dave Eliot wrote: >I don't know where to start to try this. > >I have the following fields ...

Data Entry
I know that you can go to tools/options/edit to change the next cell after you enter. I would like to go across the row instead of down the column. Is there any way to make that selection just for a workbook, . The change seems to be global until you change the preference again. Thanks, one way is to add this code in your sheet code Private Sub Worksheet_Change(ByVal Target As Range) ActiveCell.Offset(-1, 1).Activate End Sub play with it. Cesar Zapata John wrote: > I know that you can go to tools/options/edit to change the next cell after > you enter. I would like to ...

Import data from Money 2002 to Excel
Hi, I'd like to import some transactions history from Money 2002 into Excel. Does anybody know how I can do this? Thx. In microsoft.public.money, Nick wrote: >Hi, > >I'd like to import some transactions history from Money >2002 into Excel. Does anybody know how I can do this? Prepare a report in Money and right-click into it. You can copy and paste or choose Export.... For more sophisticated information access, search Google for Moneylink. ...

trouble changing data type
I am running MS Access 2007 on Windows XP Professional 2002 (SP2). I need to change the data type of a single field in a table containing just over 1.4M records in order to link it with another table and run a Make-Table Query, but Access is telling me I do not have enough memory to change the data type. I have 1GB of RAM and a Pentium 4, 2.79 GHz processor. Am I out of luck based on the equiptment I am working with, or is there a way around this? Much appreciated. -- Kevin Philadelphia, PA First backup your database. Next Compact and Repair. Third add the new field datatype. C...

Exract Data And Error Msg
ERROR MSG: Microsoft Excel cannot determine which row in your list or selection contains column labels which are required for this operation I can’t figure out why: The names of my Columns are as follows: ITEM QTY DAYS TOTAL I formatted them as Text. How do I make Excel know they are column labels and furthermore… I am composing an invoice for my rental business. I have set up a sheet with all the items that I rent. The cost of each item is determined by the number of items time the number of days rented. I would like to extract, to a separate location,(sheet 2) only the items that we...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Time entered (and handled as data) in simple minutes and seconds.
Using Excel 2003. How can I get Excel to display and handle entries for duration in minutes and seconds only (without having to enter a 4'30" entry as 0:04:30)? I have gotten as far as getting it to display as "04:30.0", but all I need is single digit minutes (data on this spreadsheet will never go over 9:59) and double digit seconds (and to be able to enter them simply as in "4:30"). I couldn't find that as an option in the ones listed for time in the cell format dialogue box. Right now I have to enter 0:04:30 in order to get it to work right. Is t...

Changing font size in data validation drop-down lists
In Excel 2003, I created the value list on a separate worksheet and labled the range so I could use it in the data validation wizard on a different worksheet. The list is working fine; however, the font size in the drop-down list is too small to comfortably read. So far, I have tried: Making the font in the list larger. Formatting larger font size in the active cell with the drop-down button And, creating the list on the same worksheet. All three remedies have not increased the size of the font in the drop-down list. Suggestions are welcome. Great Optimism, Dutch Driver There is n...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > \$0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Grouping mail by From and To
I would like to be able to create a view which groups email messages by sender AND recipient. In other words I would like each group heading for a given name to contain all the email sent to that name plus all the emails received from that name. Seems like an obvious thing to want to do but is it possible and, if so, how? I don't think you can group by two fields like that (either field having a common value). You could create a search folder that will group mail from & to certain people that that's limited to one contract per search folder <john@antell.org> wrote in ...