Rolling up output in a query

[Guest]

version: Access 2003 SP2

I have a table that categorizes projects, project resource types and project
resource names. There is one project ID per project, multiple resource types
within the project and multiple resource names within the resource types. I
want to find a way to do a query that roll up the resource type and list the
applicable resource names separated by a comma on one record line. So if I
do a simple query asking for resource type and names by project, I get this:

Project ID Resource Type Resource Name
12345 A **
12345 A ##
12345 B !!
12345 B ^^

I would like this:

Project ID Resource Type Resource Name
12345 A **, ##
12345 B !!, ^^

Much obliged for any help! Thank you!

 

[Guest]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Thanks! That was helpful. I need to separate with a semi-colon though and I
am not sure how to specify that even though the instructions say that you
can. Do you have a suggestion?

 

[Guest]

Go to the database window, Modules and open the basConcatenate module in
design view. At the top you will see the below --

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = " ") _
As String
'Created by Duane Hookom, 2003

Add a semicolon after the first quote as below. Save and close the module
and visual basic editor.
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "; ") _
As String
'Created by Duane Hookom, 2003