Concatenate values from multiple rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
Back
Top