Hi all
I am trying to create a function which can be used within the QBE grid
in Access.
The function is to calculate the median of a set of data.
I've written such a function for use in vba routines or on forms
(below) but am a little stuck in how to make it available for use
within a query. The ideal would, I suppose, be that it got added to
the list of functions available in the Total row in a totals query,
but I'm assuming that this list is fixed and I can't add my function
to it.
So, I'd happily adapt it so that I could have (using the products
table in Northwind as an example) CategoryID grouped and an expression
in the second column such as median(UnitPrice).
However, this poses me two problems.
Firstly, although I can see my function to put in an expression in the
second column, I don't know how to write the argument and its
calculation in my function - do I use a variant, would the data be an
array, etc.
Secondly, and related to this, it seems that - even if the data is
grouped - the function is being called for every row of data. I can
use that to build an array (or whatever) of the values across which to
calculate the median, but how do I then return a value - it seems that
I need an event along the lines of "LastPieceOfData" to trigger the
return value from my function, but of course events don't exist for a
query...
Hope this makes sense, and that someone may have a clue how to do
this!
If it helps, I've posted my RecordsetMedian function below, but the
issue is not with the maths so much as the technicalities of calling
and returning values from this function!
Regards
Andrew Richards
-------------
Function RecordsetMedian( _
TableName As String, FieldName As String) _
As Double
'Function to return the median average
'from a column within a table
'INPUTS: the table name and the field name - strings
'OUTPUT: the median as a double
Dim rs As ADODB.Recordset
Dim dblVal1 As Double
Dim dblVal2 As Double
Dim blnEvenNum As Boolean
'In case there's a space in the table name, 'put it in square brackets
TableName = "[" & TableName & "]"
'In case there's a space in the field name, 'put square brackets
FieldName = "[" & FieldName & "]"
'Create the recordset based on input values Set rs = New
ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
'Set up source and sort data by column required
.Source = "Select " & FieldName & " from " & TableName _
& " Order by " & FieldName
.CursorType = adOpenStatic
.Open
'Are there an even number of records?
If .RecordCount Mod 2 = 0 Then blnEvenNum = True
If Not blnEvenNum Then
'odd number of records -
'Add one to the number, divide by 2 to get
'the record number required, then
'subtract 1 because it's a zero based array
.Move ((.RecordCount + 1) / 2) - 1
RecordsetMedian = .Fields(0).Value
Else
'Even number of records -
'Need to find the average of the records
'just before and just after the mid-point
.Move (.RecordCount / 2) - 1
dblVal1 = .Fields(0).Value
.MoveNext
dblVal2 = .Fields(0).Value
RecordsetMedian = (dblVal1 + dblVal2) / 2
End If
.Close
End With
Set rs = Nothing
End Function
-------
|
