Rolling up output in a query

B

Beth

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!
 
B

Beth

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?
 
K

KARL DEWEY

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
 
Top