macro for massive 'find and replace'

[Jerry Crosby]

I'm working on a .dbf table from another program, trying to manipulate the
data so it can be used in a mailmerge letter. Anyway, the table has
numerous fields. Some of those fields have a person's first and last name
(in one field) ex.: "John Smith"

I want to do a find and replace and replace all the John Smiths with "John
and Mary Smith".

BUT, I have about 80 such possible names to change. (John Smith, Joe Brown,
George Cook, etc. each need to be replaced with John and Mary Smith, Joe and
Betty Brown, George and Hilda Cook, etc.)

Is there a way to do a massive 'find and replace' given this scenario via
code? macro? or what?

Thanks in advance.

Jerry

 

[Douglas J. Steele]

If you're updating values in a table, you're better off running Update
queries rather than using the Find & Replace dialog.

 

[Jerry Crosby]

I don't think that will work in my situation. Say in Field1 I have the
potential for 80 names that all need to be updated. I'd need to run the
update query 80 times, once for each different name to update, correct?
Hope that makes sense; at least I get an error when I tried it in one query.

Jerry

 

[Douglas J. Steele]

One approach would be to build a second table consisting of two fields
"NameToChangeFrom" and "NameToChangeTo". You can then join that table to
your main table on the NameToChangeFrom, and change it to NameToChangeTo.

 

[Jerry Crosby]

Thanks, Doug. I understand the concept and I've made the second table and
established the one-to-many relationship between it and the main table, but
I'm stuck on how to update the main table. I tried an update query, but
didn't know how to write "take this field in this table and update it to the
matching field in this other table."

Sorry to belabor a point, but I'm kinda slow.

Jerry

 

[Douglas J. Steele]

The SQL would look something like:

UPDATE tblChanges
INNER JOIN tblMyData
ON tblChanges.NameFrom = tblMyData.UserNm
SET tblMyData.UserNm = [tblChanges].[NameTo];

I created this by dragging tblChanges and tblMyData into the query builder,
and joining them by linking tblChanges.NameFrom to tblMyData.UserNm. I
changed the query to an Update Query, and indicate to update to
[tblChanges].[NameTo]