Update Query question

[blaine011]

I have a Table in this database, which can definitely be normalized quite a bit.

I need to create a query to replace city names with ref numbers.

I created a table from a query which selected all of the distinct names from the city field in the main Order Information table, called City. After that I added a field in the city table called cref. Which I then linked with relationships.

Now what I want to do in the Order Information table, is replace all of the city names with just the corresponding cref number from the City table.

How can I do this?

 

[mikevh]

UPDATE [Order Information]
SET [Order Information].City = City.CityRef
WHERE [Order Information].City = City.City;

 

[dhookom]

Make sure the City field in the City table (consider a naming convention) is the primary key. You can then place both tables in a query and join the City fields. Change the query to an update query and enter [cref] in the Update To of the appropriate field in the OrderInformation table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.