How do I query multiple rows into a single string in Access?

[Guest]

I'm working on a Microsoft Access query. I'm trying to pull a query on
classes taught by faculty for their departments. Some faculty teach 1
section, most teach 3, and some teach more than 3. Each class is in a
different row in the database. (They are stored with numeric codes, but I've
made this example human readable. ;-)

INSTRUCTOR, COURSENUM
"Jane Doe", "ENG101"
"John Smith", "PSYC274"
"John Smith", "PSYC350"
"John Smith", "PSYC423"
"Chris Wood", "FREN101"
"Chris Wood", "FREN102"
"Chris Wood", "FREN220"
"Chris Wood", "FREN221"

However, I want to be able to aggregate these course numbers from multiple
rows into a single string so the query comes out with one instructor per
line, like so.

INSTRUCTOR, COURSES
"Jane Doe", "ENG101"
"John Smith", "PSYC274 PSYC350 PSYC423"
"Chris Wood", "FREN101 FREN102 FREN220 FREN221"

Any ideas on how to massage the data like this in Microsoft Access? If
everyone had only two courses, I would use the expression Min(COURSENUM)+"
"+Max(COURSENUM). For "John Smith" in the example above, this gives a
COURSES string of "PSYC274 PSYC423" and omits the middle class.

What can I do to aggregate all courses for one instructor into a single
string in a Microsoft Access query when there are variable number of multiple
rows?

Thanks!!

 

[Guest]

Check out this site (http://www.mvps.org/access/modules/mdl0008.htm), it
gives an example and some code.

HTH
Dale

 

[Guest]

use can use wild cards in the q say psy***

have a look at the following site some useful q tutorials

Danny
http://www.datapigtechnologies.com/

 

[Duane Hookom]

There is similar Generic Concatenation code with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.