Aggregate Functions for Text??

[Chris Brady]

In MS Acess it is possible to use aggregate functions to summarise
numeric data such as count([field]), sum([field]), min([field]),
max([field]), ave([field]), etc.

However is it possible write new aggregate functions that process text
data - for example unique([field]) or concatenate([field])?

What I need to do is to join together the contents of a text field
using group by in the query.

The data in the field will be something like: aa, bb, cc, dd, ee, aa,
ff, ee, cc, ... in no particular order.

I want to group this so that the result is: "aabbccddeeff"

Many thanks - Chris B.

[Doug Hutcheson]

Chris,
I am not terribly clear as to what you want to achieve, but it is
certainly possible to write your own VBA routine and use it as a
function inside a query.

If you are talking about a text field which contains "aa, bb, cc, dd,
ee, aa, ff, ee, cc, ..." and you want to reformat it as "aabbccddeeff",
this might help:

1. Assume your field is called 'strStrangeText'

2. Write a function 'fncFixStrangeString( strStrangeString as String) as
String', which:
- takes a supplied string
- parses it into the respective tokens
- places these tokens into an array
- sorts the array
- extracts the sorted tokens into a new string
- returns this string as the value of the function

3. In your query, use this function to output the string the way you
want to see it:
'SELECT fncFixStrangeString( strStrangeString ) as strNiceString ..... '

HTH
Doug

--
-----------------------
"I do not exhort you to make war without lawful reasons;
I only desire you to apply yourself to learn the art of it.
For it is impossible to govern well without knowing
the rules and disciplines of it."
(Peter the Great, writing to Tsarevich Alexis).

[Doug Hutcheson]

Oops...
Last line should be:
'SELECT fncFixStrangeString( strStrangeText ) as strNiceString ..... '
"8-\

[Chris Brady]

Doug - thank you very much for your interest and help.

It is an aggregate function I need - not to say sum the total of the
numeric data in a field but to concatenate the strings in a text
field.

Here's the problem:

In SQL there are quite a few GROUP BY functions by which count, sum,
find max or min, of grouped data in a numeric field.

The functions that achieve this are - surprisingly - COUNT(field) or
SUM(field) or MIN(field) or MAX(field), etc. However these are all
numeric grouping functions.

What I need is a grouping function to concatenate text strings in a
text field, e.g. CONCAT(field).

Example:

Original data

Best seen in Courier New font:

Original data:

Customer Identification Number
| Profile Type Code
| | Profile Category Code
4114 3 LJ
4114 AP LD
4114 B3 HS
4114 B4 DB
4114 CB DT
4115 HU HJ
4115 GF JK

But what I want to output are records with the data in the last two
fields 'grouped' i.e. the data concatenated:

Customer Identification Number
| Profile Type Code
| | Profile Category Code
4114 3 APB3B4CB LJLDHSDBDT
4115 HUGF HJJK

Chris B.

[Keri Hardwick]

Here's a concatenate function:
http://www.mvps.org/access/modules/mdl0004.htm

Keri

"Chris Brady" <chris...@yahoo.com> wrote in message
news:3bbd7f6a....@news.baplc.com...

[Chris Brady]

Thank you for the pointer - unfortunately that page is not available.

Chris B.

[Keri Hardwick]

Comes up just fine for me. What browser are you using?

Keri
"Chris Brady" <chris...@yahoo.com> wrote in message

news:3bc1c471...@news.baplc.com...