CODEWITHSUNDEEP
sqlms-access-2007
George Demeter
George Demeter

Reputation: 1

Update query based on multiple criteria

In Access 2007 I am looking to create an update query to update a region_field based on the data in city_field in the same table. I only have data in the city_field. There are only 13 cities and 5 regions - Central, West, East, North, and South. As the table grows, I don't to overwrite the previous region entries (I guess if not Null then proceed with the update). it works fine when I have only one entry in the UPDATE TO box:

UPDATE tblCustomers SET tblCustomers.region = "CENTRAL"
WHERE (((tblCustomers.office)="LA" Or (tblCustomers.office)="SF" Or (tblCustomers.office)="SD"));

How do I add the rest of the regions and the corresponding cities to this statement?

Thank you.

Upvotes: 0

Views: 993

Answers (1)

Juan Martinez
Juan Martinez

Reputation: 111

Ideally, you should create an additional table that stores only city and region. Based on what you've provided, this table would presently only contain 13 rows (one per office).

Then, you could do something like this:

UPDATE tblCustomers 
SET tblCustomers.region = (select top 1 NEW_TABLE.region from NEW_TABLE where NEW_TABLE.office = tblCustomers.office)

The "top 1" part just makes sure the subquery only returns one row. Without this, Access may not like the query. Of course, your NEW_TABLE should only contain one row per each unique office.

Upvotes: 1

Related Questions