Results 1 to 3 of 3

Thread: Median in query

  1. #1
    Join Date
    Dec 2004
    Location
    Sydney
    Posts
    1

    Unanswered: Median in query

    I am writing a statistical report for a weekly reporting database and need to include the median as well as the average in the report. The SQL for the report is:

    SELECT vw_a.Assessor, Count(vw_a.Award) AS CountOfAward, Avg(vw_a.Award) AS AvgOfAward, Max(vw_a.Award) AS MaxOfAward, Min(vw_a.Award) AS MinOfAward
    FROM vw_a
    GROUP BY vw_a.Assessor
    ORDER BY vw_a.Assessor;

    vw_a is a query that contains two fields:
    Assessor - Text
    Award - Currency (there can be many Awards for each Assessor)

    What I would like is to add another item to my select clause like:
    Median(vw_a.Award) as MedianOfAward
    with a VBA module to calculate the median. How can I do this?


    Thanks
    Caitlin

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Median Function

    Hi

    An intersting problem!

    As there is aparently no Median function I has written one that can be used in queries or code, as follows:-

    Code:
    Option Compare Database
    Option Explicit
    
    Function MEDIAN(Field As String, Table As String) As Double
        Dim SQL As String
        Dim rs As Recordset
        
        'QUERY USING REQUIRED FIELD AND TABLE OR STORED QUERY (IN ASCENDING/DESCENDING ORDER!)
        SQL = "SELECT " & Field & " FROM " & Table & " ORDER BY " & Field & " ASC"
        
        Set rs = CurrentDb.OpenRecordset(SQL)
        
        'CHECK THRE ARE RECORDS!
        If rs.BOF And rs.EOF Then
            MEDIAN = 0
            Exit Function
        End If
        
        'POPULATE RECORDCOUNT PROPERTY
        rs.MoveLast
        rs.MoveFirst
        
        'EVEN OR ODD NUMBER OF RECORDS?...
        If rs.RecordCount Mod 2 = 0 Then
            '... EVEN NUMBER GO TO RECORD BEFORE THE MIDDLE
            rs.AbsolutePosition = Int(rs.RecordCount / 2) - 1
            MEDIAN = rs(Field) / 2
            '...  AND THE RECORD AFTER THE MIDLE
            rs.MoveNext
            MEDIAN = MEDIAN + rs(Field) / 2
        Else
            '... ODD NUMBER GO TO MIDDLE RECORD
            rs.AbsolutePosition = Int(rs.RecordCount / 2)
            MEDIAN = rs(Field)
        End If
        
    End Function
    
    
    
    Sub test_Median()
        Dim rs As Recordset
        Dim SQL As String
        
        'TEST QUERY
        SQL = "Select sum(Award) as sum, Avg(Award) as Average, Median(""Award"",""vw_a"") as Median FROM vw_a"
        
        Set rs = CurrentDb.OpenRecordset(SQL)
        
        If rs.BOF And rs.EOF Then
            MsgBox "No records!"
            Exit Sub
        End If
        'CHECK VALUES RETUNED
        MsgBox "Sum = " & rs(0) & ", Average = " & rs(1) & " and Median = " & rs(2)
    End Sub
    I has included a test routine to test it in query in code, but it can be used in query desiner also.

    Note that the MEDIAN arguments are strings.
    Also note that the AbsolutePosition propery is zero based.

    Hope this is of some help or gives you some ideas, but if not it was an intereting exercise anyway!

    MTB

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    MTB you said . . .

    "Note that the MEDIAN arguments are strings.
    Also note that the AbsolutePosition propery is zero based."

    How do "strings" apply here and the same for "AbsolutPosition.' What is the caution?

    Thanks

    Rick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •