access query with multiple where clauses

Question

I am trying to run a query in access which carries out a two stage currency conversion. It takes an exchange rate from a Exchange Rates Table in the transactional Currency and then takes the Exchange rate of the Region.

Is there any way to do this within the one query. I have attempted below but am getting syntax errors.

UPDATE REPORT 
SET REPORT.[Conversion Rate] = 
(

(
Exchange_Rates.Rate
Where
Exchange_Rates.code = REPORT.[Transaction Currency Code]
)

/

(
Exchange_Rates.Rate
Where
Exchange_Rates.code = REPORT.[Regional Currency Code]
)

)

Answer 1

You can use the access UPDATE JOIN syntax to JOIN the exchange rate table to the Report table:

UPDATE  (Report
        INNER JOIN Exchange_Rates tr
            ON tr.code = Report.[Transaction Currency Code])
        INNER JOIN Exchange_rates reg
            ON reg.code = report.[Regional Currency Code]
SET     [Conversion Rate] = tr.Rate / reg.Rate;

N.B. You need to join twice to get both exchange rates

Another option is to use the DLOOKUP function:

UPDATE  REPORT 
SET     [Conversion Rate] = Dlookup("Rate", "Exchange_Rate", "Code = " & Report.[Transaction Currency Code])
                            /
                            Dlookup("Rate", "Exchange_Rate", "Code = " & Report.[Regional Currency Code])