I think there is no built-in way to do this, and I am not aware of a brilliant shortcut. But you might write a UDF that collects the desired information for each group (with "groupfield" as parameter, in your example).
>I have a query that combines records together like the following.
>
>select mytable.groupingfield, sum(mytable.numberfield), mytable.textfield from mytable group by mytable.groupingfield
>
>Obviously, mytable.textfield will only show one row of data in each group, the other rows are discarded. But is it possible to have this field add the strings in each group together. ie. Using the above query statement on the following table.
>
>
>groupfield numberfield textfield
>1000 1 blue
>1000 1 red
>1000 2 green
>1001 1 yellow
>1001 1 brown
>
>
>The query result would be
>
>groupfield numberfield textfield
>1000 4 blueredgreen
>1001 2 yellowbrown
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)