Calculate median in query

[dcnick]

Hello:

I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.

Example (fields in the query)
Expr1
Expr2:Median(Expr1)

Thanks in advance.

 

[raskew]

Hi -

Here is a sample query for Northwind's Orders table which calls Function medianF (shown below). Try copying/pasting to a new query. You'd call the MS Median function in the same manner.

SELECT  DISTINCT
    Orders.OrderID
  , Orders.Freight
  , medianF("Orders","Freight") AS mymedian
  , [freight]-[mymedian] AS FreightDiff
FROM
   Orders;

Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight") <enter>
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

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

HTH - Bob

 

[dcnick]

Yes, it worked great! For others that may be reading this post, I want to include how I called the function, as I was unclear about this portion until I tried it in Access. I changed the code a little in that I called a query instead of a table.

I want Expr2 in Query1 to be the median of Expr1 in Query1. So, in the "field" cell of the column I'm creating for Expr2, I wrote the following:

Expr2: MedianF("Query1","Expr1")

Thanks for your help!

 

[ya5irha55an]

Median in query rather than table

What changes need to be made for this to run on a query instead of a table? If someone could post the code with the changes made, that would be great, thanks in advance!

 

[dcnick]

No changes need to be made to what I printed in my last post. The only change I made from raskew's code is how I called the function. Instead of inserting the table name, I inserted the query name in the first part of the expression.

I want Expr2 in Query1 to be the median of Expr1 in Query1. So, in the "field" cell of the column I'm creating for Expr2, I wrote the following:

Expr2: MedianF("Query1","Expr1")

So, in the first line of the function, change it to:
Function MedianF(pquery As String, pfield As String) As Single

Hope this helps

 

[dcnick]

No changes need to be made to what I printed in my last post. The only change I made from raskew's code is how I called the function. Instead of inserting the table name, I inserted the query name in the first part of the expression.

I want Expr2 in Query1 to be the median of Expr1 in Query1. So, in the "field" cell of the column I'm creating for Expr2, I wrote the following:

Expr2: MedianF("Query1","Expr1")

So, in the first line of the function, change it to:
Function MedianF(pquery As String, pfield As String) As Single

Hope this helps

 

[dcnick]

No changes need to be made to what I printed in my last post. The only change I made from raskew's code is how I called the function. Instead of inserting the table name, I inserted the query name in the first part of the expression.

I want Expr2 in Query1 to be the median of Expr1 in Query1. So, in the "field" cell of the column I'm creating for Expr2, I wrote the following:

Expr2: MedianF("Query1","Expr1")

So, in the first line of the function, change it to:
Function MedianF(pquery As String, pfield As String) As Single

Hope this helps

 

[ambrn14]

I am new to the community and a novice in Access. I have a very simple database that in used in a hospital. I really need to be able to find the median in my database.

I am using the Northwind database but I am having trouble with the method above. I have created a query with the first set of code in it. When I run this query, I get an error message "Undefined function 'medianF' in expression". I created another query with teh second set of code in it. When I run this query, I get an error message "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'." So I do not know what I am doing wrong.

Any help would be greatly appreciated!!

Thanks!

 

[stopher]

The first block of code is a query. However, the second block of code is VBA code. It needs to be put in a module.

Open the VBA editor (Ctrl-G) and and a new module (from the menu Insert=>Module). You should see a blank area except for maybe a line of text "Option Compare Database" at the top. Just paste the send block of code in the blank area.

What this has done is create a custom function, MedianF, that can be used anywhere within your project.

hth
Chris

 

[ambrn14]

The first block of code is a query. However, the second block of code is VBA code. It needs to be put in a module.

Open the VBA editor (Ctrl-G) and and a new module (from the menu Insert=>Module). You should see a blank area except for maybe a line of text "Option Compare Database" at the top. Just paste the send block of code in the blank area.

What this has done is create a custom function, MedianF, that can be used anywhere within your project.

hth
Chris

Sorry to be such a pest. Thank you so much for clarifing for me. But I followed your directions and recieved the following message when I tried to run the query: "Undefined function 'medianF' in expression." Thanks.

 

[stopher]

Can you post your database? Or send some screen shots of what you've done

 

[ambrn14]

I am using the NorthWind database from Microsoft to play in. I have attached the database with my work inclosed.

 

[stopher]

Is the database in a trusted location?

Also, I would rename the module. Just just call it modTest for now or anything except the name of your function.

When I did the able, the function was at least found. There are other errors to deal with thereafter but it's a step forward.

Chris

 

[stopher]

Ok, the fields and table names in the query that raskew originally gave doesn't correspond with the fields in your Order table.

Try this query (median on Shipping fees)

SELECT DISTINCT Orders.[Order ID], Orders.[Shipping Fee], medianF("Orders","[Shipping fee]") AS mymedian, [Shipping Fee]-[mymedian] AS FreightDiff
FROM Orders;

hth
Chris

 

[ambrn14]

I noticed that before and was getting ready to ask about it.

I retried the other code and it runs without errors.

But the median value from the table is $7.00. So the module is not correct. Now I am bummed.

THanks for all you help!!!

 

[stopher]

Are you including or excluding zeros? raskew's code excludes zeros.

 

[ambrn14]

Gotcha!! So I will just remove that part of the code if I chose to exclude, correct? Thank you so much for your help!

 

[raskew]

Hi -

If you'd care to convert your download to an mdb (I lack the later software), I'd be happy to give it a shot.

Best wishes - Bob

 

[ambrn14]

I appreciate the offer but I think that I can use the previous code.

Have a great day!

 

[PaulA]

This has been a very helpful post--thanks!

I'm getting a "Too Few Parameters.Expected 1" error when I run the code on my own database. I am using a parameter for a query that my current query (used to calculate the median) is based on. The field in question itself is a calculated field ("age") so I don't know if that would matter.

I tried declaring a querydef (which isn't included in the above code) in order to provide the parameter but it isn't working.

Any ideas how to solve this glitch?

thanks.