Exchange Rate Table

[Guest]

Hi all

I need to develop an expense system for work, we deal with people in lots of
different countries and need to calculate on different exchange rates.
Obviously since these change regularly I need them to be different per
period. How do most people cope with this?

I am thinking of fixing it monthly so my rates table will look something like:
(We need to report to our customers in Euros)

Country Jan05 Feb05 etc
UK 1.46 1.44
EUR 1.00 1.00
Swiss 0.64 0.62

or Is there a better way? Not 100% sure how I would then use this as a
lookup table? Each expense record can be traced to a country, has a currency
type and a date field.

Thanks
Sue

 

[Guest]

Hi, Sue.

For easy lookups, set up your table's fields like this:

ID, AutoNumber, primary key
Country, Text
RateDate, Date/Time
Rate, Single
CurrencyUsed

Place a unique index on Country and RateDate.

Records will be stored like this:

ID Country RateDate Rate CurrencyUsed
1 UK 1/1/2005 1.46 <Pound symbol>
2 EUR 1/1/2005 1.00 Euros
3 Swiss 1/1/2005 0.64 <Euros or Francs?>
4 UK 2/1/2005 1.44 <Pound symbol>
5 EUR 2/1/2005 1.00 Euros
6 Swiss 2/1/2005 0.62 <Euros or Francs?>
.. . . et cetera

To pick a specific month and year for an exchange rate, a query would look
like this:

SELECT Rate, CurrencyUsed
FROM tblRates
WHERE ((Month(RateDate) = 2) AND (Year(RateDate) = 2005) AND (Country =
'UK'));

.. . . where 2 is the month of February. So this would retrieve the rate and
the British Pound symbol for the UK for February of this year.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks Gunny looks good, will try out Monday when back at work!
Have a good weekend
Cheers
Sue

 

['69 Camaro]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.