Concatenating

M

Mary

Hello,
I have a unique identifier and then 1 or 2 or 3 or 4 values that are listing
out separately that I want to combine into a field like 1+2, or 2+3+4, etc.
For example 2 lines become 1:
1. ID =2 valuecol1= 2
2. ID =2 valuecol1= 3

becomes just one line where ID=2, then valuecol1= 2+3
Can you please help me? Thank you so much.
 
J

Jeff Boyce

Mary

How have you already tried this? Are you using the "&" symbol for
concatenating or the "+"? Are you handling Nulls? (if not, since Nulls
propagate, you would end up with a Null if any of the values are Null).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
M

Mary

When I use this Duane Hookum's query syntax FirstNames: Concatenate("SELECT
FirstName FROM tblFamMem WHERE FamID =" & [FamID]) I get
"Undefined Function"???
 
B

BruceM

Did you copy the module basConcatenate to your database? It contains the
custom function Concatenate, which is what you are using in the expression
you wrote.

Mary said:
When I use this Duane Hookum's query syntax FirstNames:
Concatenate("SELECT
FirstName FROM tblFamMem WHERE FamID =" & [FamID]) I get
"Undefined Function"???

John Spencer said:
Since the values are in different records you need a Concatenation
function.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

And click on Generic Function To Concatenate Child Records


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
G

Gary Walter

Mary said:
I have a unique identifier and then 1 or 2 or 3 or 4 values that are
listing
out separately that I want to combine into a field like 1+2, or 2+3+4,
etc.
For example 2 lines become 1:
1. ID =2 valuecol1= 2
2. ID =2 valuecol1= 3

becomes just one line where ID=2, then valuecol1= 2+3
Can you please help me? Thank you so much.
Click to expand...
In addition to other sage posts...

if you added another field to your table
(say "ConCat" of type Text(255) )

{replace "yurtable" with name of your table}

UPDATE
yurtable AS t1
INNER JOIN
yurtable AS t2
ON
t1.ID = t2.ID
SET t1.ConCat = ([t1].[ConCat]+" + ") & [t2].[valuecol1];

you would still have multple lines,
but a query could produce "one-line"
with a DISTINCT or a GROUP BY, i.e.,

SELECT
DISTINCT
ID,
ConCat As valuecol1
FROM
yurtable;
 
G

Gary Walter

Of course, if this was more than a
"one-time-deal," you would need
to always first run update to clear ConCat,
then the update query in previous post.

UPDATE
yurtable As t1
SET t1.ConCat="";

then

UPDATE
yurtable AS t1
INNER JOIN
yurtable AS t2
ON
t1.ID = t2.ID
SET t1.ConCat = ([t1].[ConCat]+" + ") & [t2].[valuecol1];

then use query

SELECT
DISTINCT
ID,
ConCat As valuecol1
FROM
yurtable;

Gary Walter said:
Mary said:
I have a unique identifier and then 1 or 2 or 3 or 4 values that are
listing
out separately that I want to combine into a field like 1+2, or 2+3+4,
etc.
For example 2 lines become 1:
1. ID =2 valuecol1= 2
2. ID =2 valuecol1= 3

becomes just one line where ID=2, then valuecol1= 2+3
Can you please help me? Thank you so much.
Click to expand...
In addition to other sage posts...

if you added another field to your table
(say "ConCat" of type Text(255) )

{replace "yurtable" with name of your table}

UPDATE
yurtable AS t1
INNER JOIN
yurtable AS t2
ON
t1.ID = t2.ID
SET t1.ConCat = ([t1].[ConCat]+" + ") & [t2].[valuecol1];

you would still have multple lines,
but a query could produce "one-line"
with a DISTINCT or a GROUP BY, i.e.,

SELECT
DISTINCT
ID,
ConCat As valuecol1
FROM
yurtable;
Click to expand...
 
M

Mary

I don't know what I'd do without this website - thank you - it worked
beautifully!!!

BruceM said:
Did you copy the module basConcatenate to your database? It contains the
custom function Concatenate, which is what you are using in the expression
you wrote.

Mary said:
When I use this Duane Hookum's query syntax FirstNames:
Concatenate("SELECT
FirstName FROM tblFamMem WHERE FamID =" & [FamID]) I get
"Undefined Function"???

John Spencer said:
Since the values are in different records you need a Concatenation
function.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

And click on Generic Function To Concatenate Child Records


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,
I have a unique identifier and then 1 or 2 or 3 or 4 values that are
listing
out separately that I want to combine into a field like 1+2, or 2+3+4,
etc.
For example 2 lines become 1:
1. ID =2 valuecol1= 2
2. ID =2 valuecol1= 3

becomes just one line where ID=2, then valuecol1= 2+3
Can you please help me? Thank you so much.
Click to expand...
Click to expand...
Click to expand...
 
Top