Help with Concatenating rows

  • Thread starter Thread starter Rcarman11
  • Start date Start date
R

Rcarman11

So I've looked up and down this site and have tried the examples you gave but
I still can't make my rows concatenate. I've been trying to work off of
Duane's example and have built my database the same way he has. So here it
goes this is what I have.
I have a table that looks like this and is called tbl_SubGroupBulletCopy

ItemGroupID SEQUENCE# COPY
1234 1 thick and strong tape.
1234 2 good for plastic
1234 3 clear
258 1 Red foam
258 2 12x12 sheets
300 1 Blue foam
300 2 12x12 sheets

Now what I need is a table that looks like this

ItemGroupID Data
1234 thick and strong tape. </t> good for plastic
</t>clear
258 Red foam </t> 12x12 sheets
300 Blue foam </t> 12x12 sheets

Here is what my sql command is

Data: Concatenate("SELECT Copy & </t> FROM tbl_SubGroupBulletCopy WHERE
ItemGrpID =" & [ItemGrpID])

and I get this error

Undefined function 'Concatenate' in expression.

What am I doing wrong? Thanks so much for your help.
 
This might help:

http://www.accessmonster.com/Uwe/Threads/OpenMsg.aspx/access-queries/35822?ArtID=68035f3852b6e@uwe


In your case, create a temporary table with ItemGroupID as your unique index.
So I've looked up and down this site and have tried the examples you gave but
I still can't make my rows concatenate. I've been trying to work off of
Duane's example and have built my database the same way he has. So here it
goes this is what I have.
I have a table that looks like this and is called tbl_SubGroupBulletCopy

ItemGroupID SEQUENCE# COPY
1234 1 thick and strong tape.
1234 2 good for plastic
1234 3 clear
258 1 Red foam
258 2 12x12 sheets
300 1 Blue foam
300 2 12x12 sheets

Now what I need is a table that looks like this

ItemGroupID Data
1234 thick and strong tape. </t> good for plastic
</t>clear
258 Red foam </t> 12x12 sheets
300 Blue foam </t> 12x12 sheets

Here is what my sql command is

Data: Concatenate("SELECT Copy & </t> FROM tbl_SubGroupBulletCopy WHERE
ItemGrpID =" & [ItemGrpID])

and I get this error

Undefined function 'Concatenate' in expression.

What am I doing wrong? Thanks so much for your help.
Click to expand...
 
I have a table called itemGrp that has an autoNumber field and itemGrpID. in
this table itemGrpID is unique.
This might help:

http://www.accessmonster.com/Uwe/Threads/OpenMsg.aspx/access-queries/35822?ArtID=68035f3852b6e@uwe

In your case, create a temporary table with ItemGroupID as your unique index.
So I've looked up and down this site and have tried the examples you gave but
I still can't make my rows concatenate. I've been trying to work off of
Click to expand...
[quoted text clipped - 29 lines]
What am I doing wrong? Thanks so much for your help.
Click to expand...
Click to expand...
 
Right now with your updating an AllResults field i get an error telling me
that i have a

Type mismatch in expression.

Thank you for your help.

I have a table called itemGrp that has an autoNumber field and itemGrpID. in
this table itemGrpID is unique.
This might help:
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
You might have a problem with Nulls. Check your data and exclude those
records, or use the function Nz().
Right now with your updating an AllResults field i get an error telling me
that i have a

Type mismatch in expression.

Thank you for your help.
I have a table called itemGrp that has an autoNumber field and itemGrpID. in
this table itemGrpID is unique.
Click to expand...
[quoted text clipped - 4 lines]
Click to expand...
 
Nope no Nulls.
You might have a problem with Nulls. Check your data and exclude those
records, or use the function Nz().
Right now with your updating an AllResults field i get an error telling me
that i have a
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
Right now with the query that use the Concatenate function. I have the exact
expression as Duane. My tables are the same with the same field type even. Is
it possible my version of Access doesn't recognize the work Concatenate?
Nope no Nulls.
You might have a problem with Nulls. Check your data and exclude those
records, or use the function Nz().
Click to expand...
[quoted text clipped - 4 lines]
Click to expand...
 
The technique in the link I posted doesn't use Concatenate(). Look through
it and read my responses to Basil.
Right now with the query that use the Concatenate function. I have the exact
expression as Duane. My tables are the same with the same field type even. Is
it possible my version of Access doesn't recognize the work Concatenate?
Nope no Nulls.
Click to expand...
[quoted text clipped - 3 lines]
Click to expand...
 
Is there a way to do it with out writing VBA code? I've tried kingston way
and I still can't get it to work.
 
Is there a way to do it with out writing VBA code?
Click to expand...

Not that I can think of, in any practical way. What specific problems are you
having?

John W. Vinson [MVP]
 
Thanks guys for your help. I didn't want to get into VB code b/c it's such a
small project. So what I ended up doing was using a cross tab query to turn
the rows into columns and then just concatenated the columns with an & sign.
Thanks you for all your help though. Now if you know how to get rid of html
tags by using a replace query I could use what ever help you could give me.
Thanks again
 
Back
Top