Concatenate rows

[ErinL]

Hi everyone -

I have been trying to figure out this concatenate thing on my own for two days now and I cannot get it right so I am asking for assistance.

I have found several examples of code to combine one field's contents from two different records but have not been able to get any of them to work for me.

I have a query (qryLeanAssignmentIdeaEmail) that is based on two tables (tblAssignedTo and tblEmailAddresses). Here is the sql behind the query:

SELECT tblAssignedTo.LeanIdeaNumber, tblEmailAddresses.EmailAddress
FROM tblAssignedTo INNER JOIN tblEmailAddresses ON tblAssignedTo.AssignedTo = tblEmailAddresses.Name
WHERE (((tblAssignedTo.LeanIdeaNumber)=[forms]![frmLeanAssignmentForm]![LeanIdeaNumber]));

This query produces an output that looks like this:

LeanIdeaNumber EmailAddress
2 john.doe@email.com
2 jane.doe@email.com

I need to have both emails on one line and separated by a comma. Like this:

LeanIdeaNumber EmailAddress
2 john.doe@email.com, jane.doe@email.com

Can anyone please help me in writing the code to do this? I know it can be done as I have seen many examples with replies that they "worked great" but I can't seem to get it right on my own.

Thank you in advance for your help.

 

[pbaldy]

Here's one:

http://www.mvps.org/access/modules/mdl0004.htm

There's another on Allen Browne's site:

http://allenbrowne.com/func-concat.html

 

[jdraw]

try this link
http://allenbrowne.com/func-concat.html


OOPs I see Paul responded while I was typing.

 

[pbaldy]

Fastest fingers in the west!

 

[ErinL]

WOW! Thank you both for the lightning fast responses.
These are examples that I saw and tried to adapt myself several different ways but I always get the following error:

Error 3061: Too few parameters. Expected 1.

What does this mean?

 

[pbaldy]

Usually a problem in the SQL statement, but hard to say without seeing your code.

 

[ErinL]

Here are all the variations I have tried:

SELECT tblAssignedTo.LeanIdeaNumber, ConcatRelated("EmailAddress","qryLeanAssignmentEmail") AS AssignedToEmailAddress
FROM tblAssignedTo;

SELECT qryLeanAssignmentEmail.LeanIdeaNumber, ConcatRelated("EmailAddress","qryLeanAssignmentEmail") AS AssignedToEmailAddress
FROM qryLeanAssignmentEmail;

SELECT tblAssignedTo.LeanIdeaNumber, ConcatRelated("EmailAddress","qryLeanAssignmentEmail") AS AssignedToEmailAddress
FROM tblAssignedTo;

None of them are right.

This probably goes without saying at this point but I'm barely at a beginner level when it comes to writing code. I have done a little but have basically been learning as I go.

Thank you for taking the time to help me.

 

[ErinL]

Sorry. I just viewed my post and noticed the spaces between the "ma" and the "il" in the query name "qryLeanAssignmentEmail". That was a mistake in the post. In the actual query there are no spaces.

 

[pbaldy]

I meant the VBA code, as that's where the error is coming from. Seeing that points to a possibility though, if that query contains a parameter.

The space you mention in post 8 was probably added by the forum software. I've seen it do it many times.

 

[ErinL]

Ah ha!! That was it! If I take out the parameter "forms!frmLeanAssignment!LeanIdeaNumber" from the qryLeanAssignmentEmail the concatenate query works just like I wanted it to.

Oh what a beautiful sight! Thank you for walking through this with me. Another lesson learned in the confusing world of code.

 

[ErinL]

Oops I may have spoken too quick.

I added another lean idea number so there are now two (#1 and #2). When I run the query now, it concatenates the emaill addresses for all the records whether they were assigned to that specific lean idea or not.

For example, #1 was assigned to John Doe and Jane Doe and #2 was assigned to Gina Doe. The query should show like this:

LeanNumber EmailAddresses
1 john.doe@email.com, jane.doe@email.com
2 gina.doe@email.com

But instead it looks like this:

LeanNumber EmailAddresses
2 john.doe@email.com, jane.doe@email.com, gina.doe@email.com

 

[pbaldy]

You need to supply the optional third argument.

 

[ErinL]

Oh, of course.

You're wonderful! I don't know what I would do at times without this forum! Thanks again!!

 

[pbaldy]

Happy to help!!