Find and replace

[Guest]

I have a database with approx 300,000 records. I need to find certain words
and replace them with specific words. For example, I need to find red
apples and replace all occurrences with fruit. Other than opening the table
in table view and using find red apples and replace with fruit, could I use a
query? and how would you write the query?

 

[Guest]

Before you try anything backup your data,
You can use Update query to replace the string

UPDATE TableName SET TableName.FieldName = "fruit"
WHERE TableName.FieldName="red apples"

 

[Marshall Barton]

I have a database with approx 300,000 records. I need to find certain words
and replace them with specific words. For example, I need to find red
apples and replace all occurrences with fruit. Other than opening the table
in table view and using find red apples and replace with fruit, could I use a
query? and how would you write the query?

Assuming you only want to operate on one field, this kind of
query will do it one word/phrase at a time.

UPDATE table
SET field = Replace(field, "red apples", "fruit")
WHERE field Like "*red apples*"

If you need to do this for a lot of words on a recurring
basis, then you might want to think about creating a Words
table with the words and their replacement text in two
columns. For example:
Word Replacement
red apples fruit
sirloin beef
. . .

With this set up, you can Join the two tables and do it all
in one massive (but slow) update:

UPDATE table INNER JOIN Words
ON field Like "*" & Words.Word & "*"
SET field = Replace(field, Words.Word, Words.Replacement)

 

[Guest]

Marshall thanks for you help. I am new to this. Your second recommendation
matches my requirements. I will have maybe 800 records with "red apples"
that needs to be replaced with "fruit". In the database I will have
approximately 30 food names that needs to be properly categories; i.e. red
apples to fruit, tomatoes to vegetables, wholewheat loaves to breads, and so
on.

When you say create a Word table. Do you mean create at 2 column table in
MS Word, or a 2 column table in Access? If you mean MS Word, how would you
join a Word table to an Access table. I know you can join 2 Access tables
together. As I mentioned above, I am a novice with this stuff.

Where did you get the training to become such an expert? Can you recommend
a training session for me--preferrably on-line

 

[Guest]

Ofer, thank you very much for you input. I will try this. I learned
something new from you. I would never have thought of using update.

 

[Marshall Barton]

Definitely an Access table. Sorry if I confused you by
calling the table Words, but that's what you called the
things you want to replace.

While you are putting this sort of massive update
arrangement together, be sure you test on a copy of your
data table so you can quickly undo the changes for the next
try.

You want to know how I learned all this stuff? Well, after
9 years of undergrad and post grad college, I worked for 30
years in R&D as a software developer. After I retired from
corporate life, I've been working with Access for 13 more
years. I read the Help files a lot and try experiments to
see if I can make what I read actually work in different
circumstances. Reading everything in a half dozen
interesting newsgroups can also be very educational. Bottom
line, I'm a terrible source for training recommendations.