For each where varitm is multiple fields in record on subform

[kpal29]

I have a table called Data. This table has fields labelled Client, ChargeCode, PeriodYear adn PeriodMonth.

In another table called AdjChargeCodes the following fields exist: ClientCode, FromChargeCode, ToChargeCode, PeriodYear and PeriodMonth.

I need to run a procedure that will open the Data table recordset and for each record in the AdjChargeCodes table, find the corresponding record in the Data table based on ClientCode, FromChargeCode, PeriodYear and PeriodMonth and change the ChargeCode to the value in ToChargeCode.

What is the best way to accomplish this? I am thinking just a one to many relationship update query?

 

[PHV]

Something like this (SQL code) ?
UPDATE Data AS D INNER JOIN AdjChargeCodes AS A
ON D.Client = A.ClientCode AND D.ChargeCode = A.FromChargeCode AND D.PeriodYear = A.PeriodYear AND D.PeriodMonth = A.PeriodMonth
SET D.ChargeCode = A.ToChargeCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kpal29]

Perfect, thanks.