Writing Custom Aggregate Functions

[Mike C]

Hello,

Is it possible in Access to write your own custom aggregate functions?

What I am trying to do is write a function that will take a column of state
abbreviations and output the column as a string containing the long names of
the states separated by commas. I want to be able to put the function in a
textbox in the group footer of my report using the syntax:

=GenerateStateNames([State])

This is the exact same way that Sum() would behave on a column when used in
a footer. However, I have been unable to find out a way to code the function
so that it accepts [State] as a collection of values. I am able to pass it
as string and it will return the first value in the column, but for all
intents and purposes that is completely useless.

If anyone knows how I might accomplish this, it would be a great help.

Thanks,

Mike

 

[Allen Browne]

Yes: you can OpenRecordset(), and loop through the records building up the
output string.

Some examples:
- Return a concatenated list of sub-record values:
http://www.mvps.org/access/modules/mdl0004.htm

- ECount() - an extended DCount():
http://allenbrowne.com/ser-66.html

- EAvg() - an extended DAvg():
http://allenbrowne.com/ser-68.html

A basic recordset example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

 

[Mike C]

Allen,

Thank you for the response. Essentialy looping through the records and
building the string is what I am trying to do, but I'd like to be able to use
the recordset that the report is using without having to open up a new one
within the function. I have tried setting rst to Me.Recordset but for some
reason that yields me #ERROR in the textbox. By using OpenRecordset() I'll
have to specify the SQL statement to use, which I'd like to avoid because the
query I am using for my report is fairly complex, plus I'd also like to avoid
coupling the function to this specific query.

How does Sum([Cost]) (when placed in a group footer) know to treat [Cost] as
a collection of values, and why wouldn't I be able to design my function in
the same way? The reason I'm being so stubborn about the syntax is that I
also want to be able to write a Percentile() function to use in the report as
well. I have seen multiple examples online but I'm not entirely in love with
any of them. Most of the ones I've seen require that you also specify the
table name along with the field name and they specify it as "FieldName" as
opposed to [FieldName]. I would like to be able to use Percentile([Age],
..25) for example rather than Percentile("Students", "Age", .25).

Since my query used in the report has already extracted all the data I need
to perform a calculation on, why can't I just use the data that is already
there without having to rerun a query and get it out again?

Any thoughts are appreciated,

Mike

Yes: you can OpenRecordset(), and loop through the records building up the
output string.

Some examples:
- Return a concatenated list of sub-record values:
http://www.mvps.org/access/modules/mdl0004.htm

- ECount() - an extended DCount():
http://allenbrowne.com/ser-66.html

- EAvg() - an extended DAvg():
http://allenbrowne.com/ser-68.html

A basic recordset example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

Is it possible in Access to write your own custom aggregate functions?

What I am trying to do is write a function that will take a column of
state
abbreviations and output the column as a string containing the long names
of
the states separated by commas. I want to be able to put the function in
a
textbox in the group footer of my report using the syntax:

=GenerateStateNames([State])

This is the exact same way that Sum() would behave on a column when used
in
a footer. However, I have been unable to find out a way to code the
function
so that it accepts [State] as a collection of values. I am able to pass
it
as string and it will return the first value in the column, but for all
intents and purposes that is completely useless.

If anyone knows how I might accomplish this, it would be a great help.

Thanks,

Mike

 

[Rob Ward]

I've pondered this and come to the conclusion that you're bumping up against
2 problems: 1) you are mistaking what the report objects seems to do (add all
the values in field on a report in a report footer) with what actually
happens behuind the scenes, which as I understand it is that Access creates a
further query in the background to subtotal group values, and 2) SQL/queries
just aren't very good at handling strings, and even worse at comparing
strings between rows as opposed to within rows.

I can imagine creating a solution to your problem using something horrible
like some combination of domain functions or whatever, but I definitely think
you're flying well outside the SQL envelope - a user defined function
looping through a recordset is what you're going to need to do I'm afraid.
There's no reason why you couldn't make it generic though, calling it from
within the report and passing it the name of the query on which the report is
based at run time. You could even derive the names of the fields in the query
within the function as well, though you would of course need to know how to
recognise the ones that you wanted to concatenate.

 

[Allen Browne]

No: you cannot use the recordset of the report as the source for your
function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for the response. Essentialy looping through the records and
building the string is what I am trying to do, but I'd like to be able to
use
the recordset that the report is using without having to open up a new one
within the function. I have tried setting rst to Me.Recordset but for
some
reason that yields me #ERROR in the textbox. By using OpenRecordset()
I'll
have to specify the SQL statement to use, which I'd like to avoid because
the
query I am using for my report is fairly complex, plus I'd also like to
avoid
coupling the function to this specific query.

How does Sum([Cost]) (when placed in a group footer) know to treat [Cost]
as
a collection of values, and why wouldn't I be able to design my function
in
the same way? The reason I'm being so stubborn about the syntax is that I
also want to be able to write a Percentile() function to use in the report
as
well. I have seen multiple examples online but I'm not entirely in love
with
any of them. Most of the ones I've seen require that you also specify the
table name along with the field name and they specify it as "FieldName" as
opposed to [FieldName]. I would like to be able to use Percentile([Age],
.25) for example rather than Percentile("Students", "Age", .25).

Since my query used in the report has already extracted all the data I
need
to perform a calculation on, why can't I just use the data that is already
there without having to rerun a query and get it out again?

Any thoughts are appreciated,

Mike

Yes: you can OpenRecordset(), and loop through the records building up
the
output string.

Some examples:
- Return a concatenated list of sub-record values:
http://www.mvps.org/access/modules/mdl0004.htm

- ECount() - an extended DCount():
http://allenbrowne.com/ser-66.html

- EAvg() - an extended DAvg():
http://allenbrowne.com/ser-68.html

A basic recordset example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Hello,

Is it possible in Access to write your own custom aggregate functions?

What I am trying to do is write a function that will take a column of
state
abbreviations and output the column as a string containing the long
names
of
the states separated by commas. I want to be able to put the function
in
a
textbox in the group footer of my report using the syntax:

=GenerateStateNames([State])

This is the exact same way that Sum() would behave on a column when
used
in
a footer. However, I have been unable to find out a way to code the
function
so that it accepts [State] as a collection of values. I am able to
pass
it
as string and it will return the first value in the column, but for all
intents and purposes that is completely useless.

If anyone knows how I might accomplish this, it would be a great help.

 

[Mike C]

Thanks guys,

I appreciate the help. I suppose I'll just have to pass the query to the
function and have it sort it out from there.

Mike

 

[David W. Fenton]

I suppose I'll just have to pass the query to the
function and have it sort it out from there.

Well, you should reduce it to the least complicated SQL that will
get you the right result, and return as few fields as needed, and
also use DISTINCT. I would probably just create a new SQL string
from scratch, since clearly all you're going to need is the table
that stores the state abbreviation, the table with the stores the
lookup for the full name, any tables that you need to query on. The
only output column would be the full state name field, with a SELECT
DISTINCT. So, while I can imagine your joins and criteria being
complex, the actual SELECT statement should be extremely simple.