Find and Replace against set of rules in 2nd table

Looking for a tool, or code that does the following.

Edit [partial field data] in TBL1, if [partial field data] meets criteria in
TBL2, COL1. TBL2, COL2 has the REPLACE WITH information.

Not sure if this edited data can be written directly to the TBL1, or needs
to write to a TEMP TBL.

As I will continue to be tweaking and TBL COL1 & COL2, I would like this to
be a query so that I can run it as needed.

Thanks in Advance

re: Find and Replace against set of rules in 2nd table

"Hank Rouse" <hank_top_sf@hotmail.com> wrote
[color=blue]
> Looking for a tool, or code that does the following.[/color]

Unlikely there's a "tool" that meets your specific requirements. It's quite
possible that code or a query could be developed to do it -- depending on
the details.
[color=blue]
> Edit [partial field data] in TBL1, if [partial field data] meets criteria[/color]
in[color=blue]
> TBL2, COL1. TBL2, COL2 has the REPLACE WITH information.[/color]

Can you be specific about "meets the criteria" in TBL2, COL1? And, er, what
do you mean by "[partial field data]"?
[color=blue]
> Not sure if this edited data can be written directly to the TBL1, or[/color]
needs[color=blue]
> to write to a TEMP TBL.[/color]

Are TBL1 and TBL2 related by a key/foreign key, or do you intend that TBL2
is a table of instructions for updating each Record of TBL1?
[color=blue]
> As I will continue to be tweaking and TBL COL1 & COL2, I would like this[/color]
to[color=blue]
> be a query so that I can run it as needed.[/color]

Larry Linson
Microsoft Access MVP

re: Find and Replace against set of rules in 2nd table

No Primary or Foreign key is in place at this time, as the data needs to
go through a couple of 'scrubbers' first. This scrubber is the one that
will determine other features down the road.

"[partial field data]" is just that, I am not replacing the contents of
the entire cell or field. I am only replacing part of the text, based
on the Find/Replace criteria/rules in TBL2.


*** Sent via Developersdex http://www.developersdex.com ***

re: Find and Replace against set of rules in 2nd table

TBL1

COL1
Mari Johnson
Jim Evans
Charles Stanley
Jerry Straight

TBL2

COL1 COL2
Mari Mary
Straight Strait

See, I'm simply searching TBL1, COL1 for any match in TBL2, COL1.
If a match is found, Bad data in TBL1, COL1 is replaced by TBL2, COL2


"Hank Rouse" <hank_top_sf@hotmail.com> wrote in message
news:MNptf.51$rc1.6006@news.uswest.net...[color=blue]
> No Primary or Foreign key is in place at this time, as the data needs to
> go through a couple of 'scrubbers' first. This scrubber is the one that
> will determine other features down the road.
>
> "[partial field data]" is just that, I am not replacing the contents of
> the entire cell or field. I am only replacing part of the text, based
> on the Find/Replace criteria/rules in TBL2.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

re: Find and Replace against set of rules in 2nd table

Hank Rouse wrote:[color=blue]
> TBL1
>
> COL1
> Mari Johnson
> Jim Evans
> Charles Stanley
> Jerry Straight
>
> TBL2
>
> COL1 COL2
> Mari Mary
> Straight Strait
>
> See, I'm simply searching TBL1, COL1 for any match in TBL2, COL1.
> If a match is found, Bad data in TBL1, COL1 is replaced by TBL2, COL2[/color]

How would any code or query possibly know that Mari is a "bad" version of Mary
and not simply a different person entirely? You would have to have a primary
key field that agrees between the two tables.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com