Concatenate values from multiple rows

[Guest]

Hello,

Any way to use a GROUP BY function, or some other function, and then to
concatenate the returned string values in a particular field, from each
grouping?

 

[John Vinson]

Hello,

Any way to use a GROUP BY function, or some other function, and then to
concatenate the returned string values in a particular field, from each
grouping?

See

http://www.mvps.org/access/modules/mdl0004.htm

John W. Vinson[MVP]

 

[Michel Walsh]

Hi,


Yes.


Assume you start with a table, two columns, one for the field making the
group, for illustration, we will call it TheKey. The second field, call it
Concat, is an empty (NULL) varchar(255) field.


SELECT DISTINCT TheKey , IIf(False,"",Null) AS concat INTO temp
FROM myTable;

can, as exemple, produce such a table.


Next, run a query like:



UPDATE temp INNER JOIN myTable ON temp.TheKey = myTable.TheKey
SET temp.concat = (temp.concat + ", ") & myTable.fieldToConcatenate




the result will be in table temp.

That works only for Jet.


Hoping it may help,
Vanderghast, Access MVP