Concatenate multiple matches into single field?

[Ann Scharpf via AccessMonster.com]

I'm not even sure how to phrase this for a search to see if it has been
answered before. I have a query that identifies results in a table with two
pertinent fields: program name and transfer code. A program name can appear
anywhere from 1 - 10 times in the query results because it is associated with
multiple transfer codes.

I would like to query this query and concatenate all the transfer codes that
match the same program name, so I get only one row per program name. Is this
possible?

Thanks for your help.

Ann Scharpf

 

[Guest]

Use a crosstab query.

 

[Ann Scharpf via AccessMonster.com]

Thanks for the answer but this gives results like an Excel pivot table. I
really want the transfer codes to be concatenated into a single field. I am
trying to produce a shorter results table that I can copy into Word and print
with multiple columns to fit all the data onto a single sheet that is easy to
use for visual lookup.

Ann

Use a crosstab query.

I'm not even sure how to phrase this for a search to see if it has been
answered before. I have a query that identifies results in a table with two

[quoted text clipped - 9 lines]

Ann Scharpf

 

[Guest]

You can concatenate to field of the crosstab query in another query.

[Col1] & Iif([Col2] Is Null, Null, ", " & [Col2]) & .....

Thanks for the answer but this gives results like an Excel pivot table. I
really want the transfer codes to be concatenated into a single field. I am
trying to produce a shorter results table that I can copy into Word and print
with multiple columns to fit all the data onto a single sheet that is easy to
use for visual lookup.

Ann

Use a crosstab query.

I'm not even sure how to phrase this for a search to see if it has been
answered before. I have a query that identifies results in a table with two

[quoted text clipped - 9 lines]

Ann Scharpf

 

[Duane Hookom]

Use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Ann Scharpf via AccessMonster.com]

Hi, Duane:

Thanks for the response. This looks like just what I am looking for. I am
trying to model my query on your syntax and I'm getting an error. Here is
the statement from the query (first your model, then my version):

FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" &
[FamID])

Trfs: Concatenate(“SELECT Transf FROM SchedTrfList WHERE Program =” &
[RUNSTREAM Field]

Here is the error I am getting:

The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

Do you have any idea what I might be doing wrong? I'm stumped.

Ann

Use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

I'm not even sure how to phrase this for a search to see if it has been
answered before. I have a query that identifies results in a table with

[quoted text clipped - 14 lines]

Ann Scharpf

 

[Ann Scharpf via AccessMonster.com]

BTW, Access highlights the word Transf when it displays the error message.

Hi, Duane:

Thanks for the response. This looks like just what I am looking for. I am
trying to model my query on your syntax and I'm getting an error. Here is
the statement from the query (first your model, then my version):

FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" &
[FamID])

Trfs: Concatenate(“SELECT Transf FROM SchedTrfList WHERE Program =” &
[RUNSTREAM Field]

Here is the error I am getting:

The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

Do you have any idea what I might be doing wrong? I'm stumped.

Ann

Use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

[quoted text clipped - 4 lines]

 

[Ann Scharpf via AccessMonster.com]

I finally tried renaming the Transf field to TrfCd. Now I don't get the
error about the operator anymore. But I do get an error on Concatenate() now:


Undefined function 'Concatenate' in expression.

I am running Access 2003. When I go into alphabetical Help on Functions,
Concatenate is not in the list. But Duane's database works fine for me. I
am thoroughly confused now. Can anyone help me with this?

Thanks.

Ann

 

[Ann Scharpf via AccessMonster.com]

I'm feeling pretty dopey now. I talked to a programmer I work with and she
suggested that there was a program associated with Duane's database for the
Concatenate. I looked and copied the module over to my database. Still not
working but I guess it's the DAO thing. Trying to figure that out now.

 

[Duane Hookom]

Your MDB must contain a module with the Concatenate() function. The module
must not be named the same as the name of any function.

If your fields [Program] and [RUNSTREAM Field] are text, you would need to
use:
Trfs: Concatenate(“SELECT Transf FROM SchedTrfList WHERE Program =""”
&[RUNSTREAM Field] & """")

This expression assumes SchedTrfList has a field named Program and its
values match up with the values in the field [RUNSTREAM Field] in your
query.

 

[Ann Scharpf via AccessMonster.com]

I did find module and copy to my database. Originally, the ADO lines were
uncommented. When I try to run my query that uses the Concatenate() function,
I get the error

Compile error: User defined type not defined.

So, I tried to switch the uncommented lines. Commented out the ADO and
uncommented the DAO. Then, when I tried to exit the module, I kept getting:

Run-time error '3061':
Too few parameters. Expected 1.

The error appears repeatedly.

Clearly I am not understanding something.

Ann

 

[Duane Hookom]

What is the exact SQL you are using in your main query? What are the data
types of the fields referenced in the concatenate function?

 

[Ann Scharpf via AccessMonster.com]

I have tried changing the field names and changing the sourse data from
queries to tables. (I changed queries into make table queries.) So the
current version of the SQL is this:

SELECT RunstreamList.Runstream, Concatenate("SELECT TrfCd FROM SiteTrfCodes
WHERE Runstream =""" & [Runstream] & """") AS ConcatTrf
FROM RunstreamList;

All fields are text.

When I try to run the query, I get the error:

Compile error: User defined data type not defined.

The Visual Basic window opens up and, when I click OK on the error message,
the following module text is highlighted in yellow, with an arrow in the left
margin:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

The following text is highlighted in blue (except for word "Dim"):

(Dim) rs As New ADODB.Recordset

Every time I try to exit the Visual Basic window, the compile error reappears.

 

[Duane Hookom]

Open your module and select Tools-References. Find the ADO library
(Microsoft ActiveX Data Objects library) with the highest version number and
check it. Then try to compile your application by Debug->Compile.

--
Duane Hookom
MS Access MVP
--

I have tried changing the field names and changing the sourse data from
queries to tables. (I changed queries into make table queries.) So the
current version of the SQL is this:

SELECT RunstreamList.Runstream, Concatenate("SELECT TrfCd FROM
SiteTrfCodes
WHERE Runstream =""" & [Runstream] & """") AS ConcatTrf
FROM RunstreamList;

All fields are text.

When I try to run the query, I get the error:

Compile error: User defined data type not defined.

The Visual Basic window opens up and, when I click OK on the error
message,
the following module text is highlighted in yellow, with an arrow in the
left
margin:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

The following text is highlighted in blue (except for word "Dim"):

(Dim) rs As New ADODB.Recordset

Every time I try to exit the Visual Basic window, the compile error
reappears.

What is the exact SQL you are using in your main query? What are the data
types of the fields referenced in the concatenate function?

 

[Ann Scharpf via AccessMonster.com]

Thank you so much! That did it! This is just what I need.

Would this work if I change the query back to using sub-queries instead of
make table queries? I guess I will just copy the database and try that on
the copy.

This is great.

Ann

Open your module and select Tools-References. Find the ADO library
(Microsoft ActiveX Data Objects library) with the highest version number and
check it. Then try to compile your application by Debug->Compile.

I have tried changing the field names and changing the sourse data from
queries to tables. (I changed queries into make table queries.) So the

[quoted text clipped - 30 lines]

 

[Duane Hookom]

I don't recall where, how, or why you were using either subqueries or make
table queries.

--
Duane Hookom
MS Access MVP
--

Thank you so much! That did it! This is just what I need.

Would this work if I change the query back to using sub-queries instead of
make table queries? I guess I will just copy the database and try that on
the copy.

This is great.

Ann

Open your module and select Tools-References. Find the ADO library
(Microsoft ActiveX Data Objects library) with the highest version number
and
check it. Then try to compile your application by Debug->Compile.

I have tried changing the field names and changing the sourse data from
queries to tables. (I changed queries into make table queries.) So the

[quoted text clipped - 30 lines]

What is the exact SQL you are using in your main query? What are the
data
types of the fields referenced in the concatenate function?