Concatenating fields from multiple rows

  • Thread starter Thread starter Munti
  • Start date Start date
M

Munti

Guest
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
 
What if you ran a report and just grouped it by workplace?
 
I'm not sure what you mean. Could you give an example?

Thanks,
Børge
 
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:

Code:
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.
 

Users who are viewing this thread

Back
Top Bottom