[AccessD] Multiple Currency types

Gustav Brock Gustav at cactus.dk
Sat Oct 22 07:38:23 CDT 2005


Hi John

The single problem is, of course, that exchange rates vary ...

If all you need is the historical cost, the easiest method is to have another field with the USD equivalent of the amount at the date of the transaction. Which exchange rate to use is a question of its own; you probably have an official daily list from your (central) bank in addition to buying and selling rates. And which date to use? That of shipping, delivery, registration, or payment? Here the customs authorities publish another weekly (averaged) list for calculation of duties. You would also need a third field for registration of the currency. Use the official ISO 4217 code (USD, EUR, GBP, etc.):

  http://www.xe.com/iso4217.htm 

A source for daily rates is the xe.com Currency Update Service:

  http://www.xe.com/cus/

A variation of this method is to store the foreign amount, the ISO code, and the exchange rate decided for (which in your case always will be 100 for USD). Then you can always calculate the dollar equivalent.
This method, however, violates the normalization rules because the exchange rates will be repeated. Thus you may choose to move the exchange rates to a separate table where you store 

  ISO code
  Type of exchange rate (official, buying, selling, etc.)
  Date of exchange rate change
  Exchange rate

This way you can always find any exchange rate used on a specific date by looking up the exchange rate for that currency with the newest "Date of exchange rate change" smaller than (prior to) the specific date.
That will allow you to do calculations like "What if those items purchased four months ago charged in Euro were purchased today at equivalent prices?"

/gustav


>>> john at winhaven.net 21-10-2005 19:38:36 >>>
I have a field that is used to retain the historical cost of an item and up
to now is has all been in US dollars. Just got one in UK pounds. There's
more coming up too.
 
I've never dealt with multiple currency types before. How do you suggest I
store different currencies in my table? Do I need one column for amount and
one for type of currency or is there a way to do this with formatting,
masks, etc.





More information about the AccessD mailing list