Can I suggest altering tblExchRate so it has fields like this:
- ExchDate Date this exchange rate applies to
- ExchCurr Name of foreign currency (combo box)
- ExchRate Conversion factor to US$
This normalized design has several advantages (e.g. you can add another
foreign currency at some point in the future without needing to redesign the
entire database by adding columns.) Use the combination of ExchDate +
ExchCurr as the primary key (so you can't have two inconsistent records for
the same currency on the same date.)
The next issue to solve is that you may not have a record for every currency
on every date, so the ExchDate may not match the RecDate. When this happens,
you probably want to use the most recent record from tblExchRate. You will
therefore need to get the date range that the exchange rate applies to. This
is a bit messy, but Tom Ellision explains how to create those queries in
this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
That brings you to the place where you can convert any currency to the US$
on any date. If you want to then convert it to another currency (so you're
converting Yen to Euros for example), you can add another query on top of
that to achieve that.
If you are actually living in Canada, you could either using the CDN dollar
as the base rate instead of the US$ (in tblExchRate), or follow that last
step to convert.
In the end, it may be more efficient and consistent to store the values in
your local currency, if you need to perform lots of financial operations on
the records (summing, reporting for periods, etc.)
Note that Date and Currency are reserved words, so I've suggested you rename
those fields to prevent problems. Here's a list of the field names to avoid
when designing tables:
http://allenbrowne.com/AppIssueBadWord.html