foreign currency querry and design question

[Guest]

Hi,
my intention is to build an application which would allow users to enter
data in a several local currencies and display/report data in any selected
currency

Example:
some costs will be entered in Russian Rubles, some in GBP some in EUR,
finally user will be able to see costs per contract in any selected currency
(RUB, GBP, USD)..

I want to keep a table tbRates
raID 1 2 3
raCurCode EUR GBP USD
raRate 1.44 1.00 2. 00

this table would keep an exchange rates only to a certain reference currency
(in the above example GBP) the other exchange rates should be derived from it
(EUR/USD will be derived from EUR/ GBP and USD/GBP)

table keeping costs will always include currency ID (below 2000 EUR)
.....
coAmount 2,000
coCurrID 1

I would prefer to make all calculations by SQL (not using DLOOKUP).
My question is how to construct a sql querry which would pull out correct
exchange rate from a table tbRates for a given currency

thank you in advance

 

[Stefan Hoffmann]

hi,

my intention is to build an application which would allow users to enter
data in a several local currencies and display/report data in any selected
currency
Example:
some costs will be entered in Russian Rubles, some in GBP some in EUR,
finally user will be able to see costs per contract in any selected currency
(RUB, GBP, USD)..

Store your values in EUR and use Euro triangulation, e.g.

http://www.hq.usace.army.mil/CEPA/PUBS/sep01/story6.htm

Use an unbound TextBox and use the BeforeUpdate event to calculate the
EUR amount.


mfG
--> stefan <--

 

[Guest]

I found the solution

1. added 1 column in tbCosts where I lookup exchange rate against the
reference currency (ie. CZK/GBP)
Select tbRates.raRate FROM tbRate WHERE tbRates.raRate = tbCosts.coCurrID;

2. In next column I lookup exchange rate for
ReportedCurrency/ReferenceCurrency (GBP/USD)

Select tbRates.raRate FROM tbRate WHERE tbRates.raRate = param_reporteddCurID;

then I can easily figure out exchange rete for any EnteredCurrency /
ReportedCurrency and figure out translated amount in the required (reported)
currency (CZK/USD)

thanks Supicek