1. Registered User
Join Date
Dec 2004
Location
Sydney
Posts
1

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. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805

## 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. Registered User
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
•