Hi

I need help to write a macro in order to compare 2 tables. I have table A which contains the currency and the amount. I have table B which contains the conversion rate and the currency. I need to check the currency type in table A with table B and then convert the amount in table A to EUR.

Can anyone help me with this?

Regards
Dominic

Use a query:
SELECT A.Currency, A.Amount, B.ConversionRate, (B.ConversionRate * A.Amount) as ConvertedAmount
From TableA A
Left Join TableB B on B.Currency = "EUR"

OR

SELECT Currency, Amount, (Select ConversionRate from TableB where Currency = "EUR") * Amount as ConvertedAmount
From TableA

Use a query:
SELECT A.Currency, A.Amount, B.ConversionRate, (B.ConversionRate * A.Amount) as ConvertedAmount
From TableA A
Left Join TableB B on B.Currency = "EUR"

OR

SELECT Currency, Amount, (Select ConversionRate from TableB where Currency = "EUR") * Amount as ConvertedAmount
From TableA

Hi

I tried the first Query. Access returns me an error message saying Join not supported. I tried the second. It returns me the amount (unconverted).

This is TableA:
Curr Amount
AUD 8516783.33
AUD 400000
AUD 3321175
AUD 8516783.33
AUD 11840000
CAD 200
CAD 200
CHF 324
CHF 5682
DKK 30378.13
EUR 9000
EUR 9000
HKD 34000000
HKD 34019887.67
IDR 15000
IDR 30000
IDR 161380800
THB 1000
THB 655654.85
THB 655654.85
THB 655654.85
THB 858084.75
THB 1413326
USD 1433.7
USD 1433.7
USD 7975.7
USD 9044
USD 9044

This is Table B
F1 F2
AUD 1.5692
CAD 1.3609
CHF 1.5438
DEM 1.9558
DKK 7.4524
EUR 1.0000
GBP 0.6762
HKD 9.0863
HUF 246.5492
IDR 11828.7200
INR 54.0587
JPY 141.8169
MXN 8.0748
MYR 4.4529
NOK 7.9220
NZD 1.6411
PHP 63.7800
PLN 3.5148
SEK 9.4220
SGD 1.9803
THB 48.5280
TWD 39.5000
USD 1.1718
ZAR 7.4523

So I need to check the Currency in Table A compare it with Table B and make the changes to the amount in Table A. :) Thanks so much for you kind help.

Regards
Dominic

I've modifed the query to the following.

UPDATE CoronaWeekly
SET CoronaWeekly.Amount =(SELECT (Select F2 from RATES WHERE RATES.F1 = CoronaWeekly.Curr)*CoronaWeekly.Amount AS ConvertedAmount
FROM CoronaWeekly);

But it gives me the error "operation must use updateable query". Why is this so?

Regards
Dom

Dear FoFa

This is my latest SQL query but it returns me operation must use an updateable query.

UPDATE CoronaWeekly
SET Amount =(SELECT ConvertedAmount FROM (SELECT (Select F2 from RATES WHERE RATES.F1 = CoronaWeekly.Curr)*CoronaWeekly.Amount as ConvertedAmount
FROM CoronaWeekly));

If I run the query below. I get the correct results(the converted amount) displayed as one column. Why can't I update the amount in CoronaWeekly?

SELECT ConvertedAmount FROM (SELECT (Select F2 from RATES WHERE RATES.F1 = CoronaWeekly.Curr)*CoronaWeekly.Amount as ConvertedAmount
FROM CoronaWeekly);

Thanks so much in advance. Sorry for being such trouble.

Try this select first:
SELECT A.Currency, A.Amount, B.ConversionRate, (B.ConversionRate * A.Amount) as ConvertedAmount
From TableA A
Left Join TableB B on B.Currency = A.Currency

If that works to your liking then try:
Update TableA
Set Amount = (SELECT B.ConversionRate fromTableB B on B.Currency = TableA.Currency) * TableA.Amount

but with the limit info. you have supplied, I hope you are not overwritting the original currency's amount field with the converted amount....

Hi Fofa

Thanks so much for your help :)
I've managed to get it done already.

Regards
Dom