Help with SQL syntax

[ZedtheHead]

I can't seem to be able to get the SQL right for this query.

I have two tables, TableCustomers and TableRates. Both tables have CHARGES and RATES fields. I also have a form which updates the CHARGES in the TableRates table. After these changes, an update query is run that should update the CHARGES field of the TableCustomers table depending on the value of RATES in TableCustomers. So if a record in TableCustomers has a value of 3 in its RATES field, the value of its CHARGES field is updated to the value of the CHARGES field in TableRates whose RATES field is also 3.

I've got this far:

UPDATE TableCustomers 
SET TableCustomers.CHARGES = TableRates.CHARGES
WHERE ??????;

but haven't been able to get the condition right. I'm not even sure if what I've got so far is right.

Can anyone help me out on this?

 

[Jon K]

For Rates=3:-

UPDATE TableCustomers INNER JOIN TableRates ON TableCustomers.Rates=TableRates.Rates SET TableCustomers.Charges = TableRates.Charges
WHERE TableCustomers.Rates = 3;


A parameter query:-

UPDATE TableCustomers INNER JOIN TableRates ON TableCustomers.Rates=TableRates.Rates SET TableCustomers.Charges = TableRates.Charges
WHERE TableCustomers.Rates = [Enter Rates];


(Always back up your table before running a new Update Query.)
.

 

[ZedtheHead]

Thanks for the reply.

But if I did that would I have to have a different query for each value of Rates? Rates=1, Rates=2, etc.

Is there a way I can do it so that it takes care of all values of Rates. The reason is that the values of the rates field might change from time to time.

 

[EMP]

Is there a way I can do it so that it takes care of all values of Rates.

You can just remove the Where Clause.