[AccessD] Multiple Currency types

Hi Gustav,
Good points. I'm going to use this as a model for what I'm doing. 

Thanks,
John B.

PS: Sorry to hear of the EU ruling on Feta.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Saturday, October 22, 2005 7:38 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Multiple Currency types

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.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com