Access 2000 multiple update queries

[mekon]

hi all. hope you can help

I'm trying to clean an address table I have inherited. The column shop_name has lots of similar values e.g:

"Cinema 2712 (London)"
"Post Office 253 (York)"
"Cinema 513 (Essex)"

I've created a column called shop_type, because I need to categorise these. I could create multiple queries such as

update table set shop_type="Cinema" where shop_name like "Cinema*"
update table set shop_type="Post Office" where shop_name like "Post Office*"

etc

The problem is that there are going to end up being hundreds of queries. I could put them all in a macro, but is there a more elegant solution?

Is there any way I can create a table which would summarise all of these 'like updates' e.g

NAME_LIKENESS RESULT
Cinema* Cinema
Post Office* Post Office

and then JOIN this table somehow with the table of addresses?

hope I'm making sense..

cheers

 

[dinger2121]

you can create a custom function and then use one update query with the custom function.
That will probably be your best bet.