How to create a VBA function which can be used within a query

[Andrew]

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
-------

 

[Jeff Boyce]

Andrew

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

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
-------

 

[George Nicholson]

This is an educated guess, but I think you may have most of it already.
- Make a 2nd RecordsetMedian function called FilteredRecordsetMedian and add
a criteria argument so you can specify a Category. Add supporting code to
handle the criteria as well.
-Call that function from the query: CategoryMedian:
FilteredRecordsetMedian("myTable","UnitPrice",[Category])
note: arguments are: static string, static string, dynamic field value

I am not sure this will work, or if it does what its performance would be
like, but its worth a shot.
--
HTH,
George

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
-------

 

[Andrew]

Andrew

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

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
-------- Hide quoted text -

- Show quoted text -

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]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Median.mdb" which illustrates how to do this. I use this
function in queries in the sample.

--
--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

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
-------

 

[Jeff Boyce]

Perhaps what you're finding is that the Median() function from Excel expects
a set of data, while the Access query tends to operate on a set of records.

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

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

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
-------- Hide quoted text -

- Show quoted text -

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

 

[lyle fairfield]

If you use your function in a Query as

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 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
-------

 

[Andrew]

If you use your function in a Query as

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 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
-------- Hide quoted text -

- Show quoted text -

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]

You need a to implement a Where clause in your function that duplicates the
criteria in your query. The sample that I listed earlier does this. You
might want to look at it.

--
--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

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
-------

 

[lyle fairfield]

I think I'm in here way too deep and should have just shut up.
Rereading your original post I think you want the median of price
grouped on category.
I expect that modifying the first several lines of your function would
help:

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.

If you use your function in a Query as

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 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
-------- Hide quoted text -

- Show quoted text -

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

 

[CDMAPoster]

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.

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
(e-mail address removed)

 

[Andrew]

I think I'm in here way too deep and should have just shut up.
Rereading your original post I think you want the median of price
grouped on category.
I expect that modifying the first several lines of your function would
help:

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.

If you use your function in a Query as
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 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
-------- Hide quoted text -
- Show quoted text -

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- Hide quoted text -

- Show quoted text -

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