Concatenating fields from multiple rows

[Munti]

Hi,

I've got a problem with a query. I've got a table where i store registration for conferences. It got fields like name, title and workplace etc.

I need to make a query where I select workplace distinct and then finds all the persons that has that workplace and put them into one string. Can this be done?

Example:

tblConferenceParticipants

workplace firstname lastname
--------- --------- --------
footlocker john spenser
footlocker bob lucas
magic store peter jackson
magic store gary neville

After the query the result should be

workplace names
--------- -----
footlocker john spenser, bob lucas
magic store peter jackson, gary neville

from another thread I've got this:

SELECT
DISTINCT(t1.parWorkplace),
(SELECT COUTN(*) FROM [tblConferenceParticipants] WHERE [tblConferenceParticipants].parWorkplace = t1.parWorkplace) AS COUNT
FROM tblConferenceParticipants AS t1
WHERE parConID = 5

But that will only give me a result like this:

workplace COUNT
footlocker 2
magic store 2

Thanks,
Børge

 

[calmwind]

What if you ran a report and just grouped it by workplace?

 

[Munti]

I'm not sure what you mean. Could you give an example?

Thanks,
Børge

 

[Cynoclast]

As near as I can tell this is a row level manipulation and will require VBA unless you just want a report. Queries operate on Fields/Columns, if you need to do stuff based on records you need VBA/stored procedures.

BTW a report would look something like this:

footlocker	
                          john spenser
                          bob lucas

magic store	
                          peter jackson
                          gary neville

To build it just point it at your table and tell the report that the workplace is a group header.