updating rows values using second table

[danb]

Hi there, just wondering if anyone could help me. I'm *definitely* going to be buying a bit fat 'Access for retards' book soon - but in the meantime, please bear with me...

I have been given an old table containing a load of shop names, each with a unique index, and a field called 'ShopType'. The shop type field contains string data like: 'sports, books, cosmetics'.

I'm trying to incorporate this table's data into a new table containing shop names (mainly the same shop names actually)and some new fields designed to split the shop types into different rows so they can be easily dealt with in a web application:

IsSportsShop - is one column
IsBookShop - is another column
IsCosmeticsShop - is another
etc

All of these new fields are Yes/No data type.

Question is... How do I automate the process to look for part of the string such as books in the ShopType column of the old table, and tick the IsBookShop column for that shop in the new table? I have built a query:


UPDATE ShopsNew, ShopsOld SET ShopsNew.IsBookShop=True WHERE ShopsNew.ShopName=[ShopsOld.ShopName] AND ShopsOld.ShopType LIKE *& [books] &*;


Any help would be very greatfully received.

Thanks for your time.

 

[danb]

Done it. Needed a join in there... oh, and some correct syntax of course. Silly me (?)