Find and replace phrase from Column A into Column B

[CJSwed]

Hey Guys,
I have a Table in Access 2003 which contains several columns but two of them look like this...

Table A
Column A Column B
Orange Mary found the [term] in the kitchen
Grape Mary found the [term] in the cabinet
Strawberry Mary made desert with the [term]



What I am trying to do is do a find and replace in Column B where the phrase [term] is and insert the text from column A.


So the final outcome would look like:

Table A
Column A Column B
Orange Mary found the Orange in the kitchen
Grape Mary found the Grape in the cabinet
Strawberry Mary made desert with the Strawberry


Is this possible?

Thanks,
Chris

 

[missinglinq]

You can do it using a query based on your table. You'd need to add a calculated field, called ColumnC in this example. The SQL would look like this

SELECT TableA.ColumnA, TableA.ColumnB, Replace([ColumnB],"[term]",[ColumnA]) AS ColumnC
FROM TableA;



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[CJSwed]

Thank you, that worked beautiful. Is that ColumnC absolutely needed? The reason I ask is if I had a table with the [term] found in more than one column, and randomly used would it be possible to replace wherever the phrase [term] was found.

Thanks...

 

[MajP]

This most likely only applies to a few columns I would assume.
So just extend missingling's post.
Assume it could occur in fields B, D, E ..
Which you do not need is B,D,E in your select, because those are the "non updated" columns.

SELECT TableA.ColumnA,Replace([ColumnB],"[term]",[ColumnA]) AS NewColumnB,Replace([ColumnD],"[term]",[ColumnA]) AS NewColumnD
FROM TableA, Replace([ColumnE],"[term]",[ColumnA]) AS NewColumnE, etc.;

 

[CJSwed]

Guys, thank you!

A little messing around and I came up with something like this

SELECT TableA.ColumnA, Replace([ColumnB],"[term]",[ColumnA]) AS NewColumnB, Replace([Columnc],"[term]",[ColumnA]) AS NewColumnC
FROM TableA;

which does not copy but does a direct replace. Depending on your needs this can come make things more manageable.

Really, you guys are awesome and this forum has been such a big help.

I can't say it enough... Thank you!

Chris

 

[missinglinq]

Glad we could help!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!