Find and Replace against set of rules in 2nd table

[Hank Rouse]

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. (Would prefer an Update Query to update TBL1)

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

Thanks in Advance

 

[Hank Rouse]

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

 

[Marshall Barton]

You can use an update query something like:

UPDATE Tbl1 INNER JOIN Tbl2
ON Tbl1.Col1 Like "*" & Tbl2.Col2 & "*"
SET Tbl1.Col1 = Replace(Tbl1.Col1, Tbl2.Col2, Tbl2.Col1)

WARNING - This may change more that you intend if you are
not very careful about what you use in Tbl2.Col1. Be sure
to test this on a copy of Tbl1 so you don't garble any real
data unitl you're sure it's doing exactly what you want.
--
Marsh
MVP [MS Access]

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

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. (Would prefer an Update Query to update TBL1)

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

 

[Hank Rouse]

Marshall,

This solution worked PERFECTLY!!!

Now I want to perform the following query:

Delete TBL1 Record if TBL1,COL1 contains any partial text match from
TBL3,COL1

DELETE FROM Tbl1
WHERE Tbl1.Col1 Like "*" & Tbl3.Col1

It keeps prompting me to enter a parameter. The Parameters are the contents
of Tbl3.Col1.

What am I missing?

You can use an update query something like:

UPDATE Tbl1 INNER JOIN Tbl2
ON Tbl1.Col1 Like "*" & Tbl2.Col2 & "*"
SET Tbl1.Col1 = Replace(Tbl1.Col1, Tbl2.Col2, Tbl2.Col1)

WARNING - This may change more that you intend if you are
not very careful about what you use in Tbl2.Col1. Be sure
to test this on a copy of Tbl1 so you don't garble any real
data unitl you're sure it's doing exactly what you want.
--
Marsh
MVP [MS Access]

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

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. (Would prefer an Update Query to update TBL1)

As I will continue to be tweaking and TBL2 COL1 & COL2, I would like

this
to

be a query so that I can run it as needed.

 

[Marshall Barton]

You need to use the same kind of Join so the query
understands what Tbl3 is:

DELETE Tbl1.*
FROM Tbl1 INNER JOIN Tbl3
ON Tbl1.Col1 Like "*" & Tbl3.Col1 & "*"

***REPEAT PREVIOUS WARNING***
--
Marsh
MVP [MS Access]

This solution worked PERFECTLY!!!

Now I want to perform the following query:

Delete TBL1 Record if TBL1,COL1 contains any partial text match from
TBL3,COL1

DELETE FROM Tbl1
WHERE Tbl1.Col1 Like "*" & Tbl3.Col1

It keeps prompting me to enter a parameter. The Parameters are the contents
of Tbl3.Col1.

What am I missing?

You can use an update query something like:

UPDATE Tbl1 INNER JOIN Tbl2
ON Tbl1.Col1 Like "*" & Tbl2.Col2 & "*"
SET Tbl1.Col1 = Replace(Tbl1.Col1, Tbl2.Col2, Tbl2.Col1)

WARNING - This may change more that you intend if you are
not very careful about what you use in Tbl2.Col1. Be sure
to test this on a copy of Tbl1 so you don't garble any real
data unitl you're sure it's doing exactly what you want.

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


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. (Would prefer an Update Query to update TBL1)

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