Calculate Median value

AnnPhil

Registered User.
Local time
Today, 17:49
Joined
Dec 18, 2001
Messages
246
What is the easiest way to calcuate a median value for a group of values?
 
Hi -

Here's a sample function that returns the median. Try it against Northwind's Orders table:

Code:
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 hom 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
 
more help please?

Thanks, i got it to work in Northwind but can not see how to apply in my database. I have a survey database. My table's fields are surveyID, questionID, and rating. I have a groupby query that groups my questions by category and then returns the average for the rating of each question in the category. I then have a report to show the data. I can not see how to use the function in the query or the report to get the correct median values per question.

My final results should show in the report a column for Question, Average rate and Median rate for each question per category. Instead I get the same value in the Median column for every question. What it is giving me is the median value for the category repeatedly.

I feel that i am getting close but could use a little more help if you don't mind. I am not a programmer. Any help is greatly appreciated.

Thanks
 
Ann -

Could you provide some sample data as to where it's going wrong.

Bob
 
Here is a table that has a sample of questions with their rating, this is a survey database. I need to find the median for each question. Your function will work if i create a query for each question and then put your function in a report or form. I have 99 questions. would like not to have to create that many queries. Is there a way to put your function in a "group by" query that would group by question and then perform the function on each question?

I really appreciate any help with this.

Thanks
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom