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
-------
I am not sure this will work, or if it does what its performance would be
like, but its worth a shot.
--
HTH,
George
"Andrew" <andrewm...@yahoo.co.uk> wrote in message
news:68f82681-05f1-4b92...@f63g2000hsf.googlegroups.com...
If your eventual goal is to calculate medians rather than the exercise of
creating a function, consider using a Reference to the Excel object model,
which DOES have a median function, if I recall.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Andrew" <andrewm...@yahoo.co.uk> wrote in message
news:68f82681-05f1-4b92...@f63g2000hsf.googlegroups.com...
Thanks for the suggestion.
However, I don't think it really helps my cause, because although that
will allow me to call the median function (which does indeed exist)
within VBA, it still won't let me access it from a query...
:-(
Having said that, now that I'm stumped by this, it's has at least in
part become an issue of "I want to know how it's done - there MUST be
a way!!!" :-)
Thanks again for the thought, though.
Andrew
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
"Andrew" <andrewm...@yahoo.co.uk> wrote in message
news:68f82681-05f1-4b92...@f63g2000hsf.googlegroups.com...
Good luck! (any chance you could use Access to extra the data, export it to
Excel, and run your Median() function from within Excel?)
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Andrew" <andrewm...@yahoo.co.uk> wrote in message
news:19a1ed82-fa26-43e5...@p25g2000hsf.googlegroups.com...
SELECT Name, RecordsetMedian('Customers','ID') AS Median
FROM MSysObjects
WHERE Type=1 And HasColumn([Name],'ID')=True;
It is called only once as the query is not sending any dynamic data
(field values) to the function.
My function, HasColumn, on the other hand, is called for each row in
MSysObjects, 47, because it's sending a field value ([Name]) to the
function, HasColumn.
If we change the Query to
SELECT Name, RecordsetMedian([Name],'ID') AS Median
FROM MSysObjects
WHERE Type=1 And HasColumn([Name],'ID')=True;
so that your query passes a dynamic parameter [Name] to your function,
RecordsetMedian, then your function is called 17 times. My function,
HasColumn, is called 47 times; perhaps it's of interest that it is
called 47 times, once for each row in MSysObjects, to identify the 17
rows meeting the criteria FIRST, and then your function is called 17
times once for each of those rows. To reiterate, your 17 calls don't
occur until after all my 47 calls are made.
Hi
I'm sorry - I must be being really dense here, but how does this help
me? I get your point that the filtering is done through the where
clause before the functions are applied to the select list, but I'm
not entirely sure where that leaves me in terms of answering the
issues I outlined...?
I'm sure it's just me, but if you can let me know where I can apply
what you've said, I'd be grateful!
Andrew
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
"Andrew" <andrewm...@yahoo.co.uk> wrote in message
news:68f82681-05f1-4b92...@f63g2000hsf.googlegroups.com...
Function RecordsetMedian( _
TableName As String, _
FieldName As String, _
Optional FieldFilter As String, _
Optional FilterValue As Variant) _
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
If VarType(FilterValue) <> 0 Then
If VarType(FilterValue) = 8 Then
.Source = .Source & " Where " & FieldFilter & " = '" &
FilterValue & "'"
Else
.Source = .Source & " Where " & FieldFilter & " = " &
FilterValue
End If
End If
.Source = .Source & " Order by " & FieldName
rest of function is unchanged
SELECT Products.Category,
Count(Products.[List Price]) AS [CountOfListPrice],
Avg(Products.[List Price]) AS [AvgOfList Price],
RecordSetMedian('Products','List Price','Category',[Category]) AS
[MedianOfListPrice]
FROM Products
GROUP BY Products.Category
ORDER BY Products.Category;
In this case the function is called 16 times, once for each group.
Results (Access 2007, Northwindw 2007) are:
Category CountOfListPrice AvgOfList Price MedianOfListPrice
Baked Goods & Mixes 4 $11.92 $11.25
Beverages 5 $17.00 $14.00
Candy 1 $12.75 $12.75
Canned Fruit & Vegetables 8 $6.19 $1.50
Canned Meat 3 $8.13 $4.00
Cereal 2 $4.50 $4.50
Chips, Snacks 1 $1.80 $1.80
Condiments 3 $15.00 $13.00
Dairy Products 1 $34.80 $34.80
Dried Fruit & Nuts 5 $23.95 $23.25
Grains 1 $7.00 $7.00
Jams, Preserves 2 $53.00 $53.00
Oil 1 $21.35 $21.35
Pasta 2 $28.75 $28.75
Sauces 3 $26.02 $21.05
Soups 3 $4.50 $1.95
I may be totally off base here, of course, and will leave this to
someone who understands what's needed better than I do.
Personally, for medians I skip the function entirely and just use SQL:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/b24f513ff5abf10a
James A. Fortune
CDMAP...@FortuneJames.com
Hi
Sorry to have been so long getting back to you - I've been away for a
little while and not able to access the Internet much...
Anyway - this worked well, and I'm grateful to you for your help.
Regards
Andrew