Access Access 2007 - Update Query

[Jerry Tack]

Can you do a multiple update with multiple criteria in an update query?

I am trying to update a field in a table that will have different values depending on the two criteria filters IE If a vehicle type = Car and the Post Zone = SO update the Rate field to £1.15. If the vehicle type = MPV and the Post Zone = SO update the Rate field to £1.20 etc etc. Different Post Zones will have different Rates so there will be about 8 different rates.

 

[apr pillai]

1. Create a separate table with the Postal Zone and Rate Fields.
2. Create all 8 records with the Postal Zone Code and Rate values.
3. Create an Update Query with main Table along with the newly created Rate Table and link both tables on Postal Zone Code.

Sample SQL is given below:

UPDATE Table1 INNER JOIN Table2 ON Table1.PostalCod = Table2.PostalCod SET  Table1.Rate = [NewRate];

You may have several records of the same Postal Zone Code in the Main Table. All of them will be updated with the same Rate.