change multiple items in one field

[Guest]

Hello,

I have a table setup that has multiple records (over 1000). There is one
specific field that has items in it that need to be changed. For example,
about 300 of the listings has the word basketball, and I need it to be
changed to the number 9. another 200 has baseball, which needs to be changed
to the number 8. There are about 10 others to do this with as well.

I can do an update query, which will only allow me to do one of them at a
time. I can do find/replace, which again would only do one at a time. I
would like to do some sort of action where I can make a table, list what I
want the changes to be, then run it.

I need to be able to do this multiple times over and over again without
having to run multiple find/replaces or update queries. Any ideas on how I
can do this?

 

[Douglas J. Steele]

Since you're changing from text to number, you should probably add a new
numeric field to your existing table, rather than reusing the text field.

Create a table that has the conversions in it, one row for each.

Create a query that joins the existing table to that new table. As the
Update To field under the new numeric field you added to your existing
table, put [NameOfNewTable].[NameOfFieldContainingNumber]