Currency Rate

[Rony]

Hi
I have table called Curr_table
and the fields are Curr_code , and the items under that (USD /GBP/EUR)
Curr_rate , and the rates (0.295 /0.595 /0/335)

and another table called Fx_ladder
whereby all the currecies are listed with its repective fields
e.g(field) Dealtcurr and dealtamt
I need to extract only the curr_rate from curr_table_ usd
something like this,

Result :iif ([Dealtcur]="usd", i need to to extract usd rate from curr_table
e.g 0.295

How can i do that
Thanks in advance

 

[Marshall Barton]

I have table called Curr_table
and the fields are Curr_code , and the items under that (USD /GBP/EUR)
Curr_rate , and the rates (0.295 /0.595 /0/335)

and another table called Fx_ladder
whereby all the currecies are listed with its repective fields
e.g(field) Dealtcurr and dealtamt
I need to extract only the curr_rate from curr_table_ usd
something like this,

Result :iif ([Dealtcur]="usd", i need to to extract usd rate from curr_table
e.g 0.295

Add both tables to the query and drag the Dealtcur field
over to the Curr_Code field. Then you can add the Cur_Rate
field the the query's field list.

The query's SQL will end up looking like:

SELECT Fx_ladder.*, Curr_table.Curr_rate
FROM Fx_ladder INNER JOIN Curr_table
ON Fx_ladder.Dealtcur= Curr_table.Cur_Rate

 

[Rony]

Hi
I have two fields Dealtcurr and Coucurr , i need to apply rates from
currency tables the rates to both of the fields. I tried as you have
mentioned it applies for one field only other one query ignores.
Thanks
--
Ron

I have table called Curr_table
and the fields are Curr_code , and the items under that (USD /GBP/EUR)
Curr_rate , and the rates (0.295 /0.595 /0/335)

and another table called Fx_ladder
whereby all the currecies are listed with its repective fields
e.g(field) Dealtcurr and dealtamt
I need to extract only the curr_rate from curr_table_ usd
something like this,

Result :iif ([Dealtcur]="usd", i need to to extract usd rate from curr_table
e.g 0.295

Add both tables to the query and drag the Dealtcur field
over to the Curr_Code field. Then you can add the Cur_Rate
field the the query's field list.

The query's SQL will end up looking like:

SELECT Fx_ladder.*, Curr_table.Curr_rate
FROM Fx_ladder INNER JOIN Curr_table
ON Fx_ladder.Dealtcur= Curr_table.Cur_Rate

 

[Marshall Barton]

I have two fields Dealtcurr and Coucurr , i need to apply rates from
currency tables the rates to both of the fields. I tried as you have
mentioned it applies for one field only other one query ignores.

Two fields, huh? You never mentioned a Coucurr field
before. Maybe you need to use another join on the other
field, or maybe the rates are the same for both fields, or
maybe it's something else???

Would you care to explain how this new field fits into the
scheme of things?

 

[Rony]

Thanks for your reply, In short i will explain

Dealt_curr Curr_rate Cou_Curr
Curr_rate
---------- ------------
------------ -----------
Buy EUR Sell
USD
Buy CHF Sell
USD
Buy GBP Sell
EUR
Buy EUR Sell
CHF

All I need to do is link the Curr Rate from Curr_Table to Both Dealt_Curr
and Cou_Curr to value at market rate available from Curr_rate (local
Currency).

Thanks
Rony

 

[Marshall Barton]

Thanks for your reply, In short i will explain

Dealt_curr Curr_rate Cou_Curr
Curr_rate
---------- ------------
------------ -----------
Buy EUR Sell
USD
Buy CHF Sell
USD
Buy GBP Sell
EUR
Buy EUR Sell
CHF

All I need to do is link the Curr Rate from Curr_Table to Both Dealt_Curr
and Cou_Curr to value at market rate available from Curr_rate (local
Currency).

It think we only have to add another Join to the previous
query:

SELECT Fx_ladder.*,
B.Curr_rate As BuyRate,
S.Curr_rate As SellRate
FROM Fx_ladder
INNER JOIN Curr_table As B
ON Fx_ladder.Dealtcur = B.Cur_Rate
INNER JOIN Curr_table As S
ON Fx_ladder.Cou_Curr = S.Cur_Rate

But I'm not sure what the names should be because your
example above has column headings that are clearly not the
fields' real names.

 

[Rony]

Hi Marshall
I understood the concept, with little modifcationit worked.
Thanks for your help