Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    3

    Unanswered: Foreign Exchange Query

    I have built an accounting application that deals with foreign currency. The main query that converts local currency ammounts into USD has the following sql statement to make the conversion:

    Rev_LCY*(select rate from tbl_Exchange where tbl_Products.currency=tbl_Exchange.currency and tbl_Exchange.year=Tbl_Monthly.year and tbl_exchange.month=tbl_Monthly.month ) AS Rev_USD

    tbl_Products is where the currency code is stored i.e. GBP, EUR, USD... for each product.

    tbl_Exchange has monthly exchange rates for 10+ currencies.

    Is there a better way to convert than using a select query inside another select query? My application works right now, but I am limited in the way I can query the Rev_USD calculation in later queries.

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi "J"

    first off I suspect it depends on what you are looking to do with the converted cash data.

    If its to reconcile a purchase for instance for that time frame from "Other" currency then you can you a simpel macro or code to take foreign cost and save to USD. or save both in table so that you keep historical cash flow (sometimes very usefull)

    I have all exchange rates in a table - these are called by a query to a sub form. This is used to convert a parent forms textbox Ive named COST.
    This way what ever I transact in I call that country and the exchange, in my cast Pound Sterling that gives me a chance of using a simple setvalue macro and a command to save that converted rate against the product. - keeping the historical original foreign charge value can help to sort any issues at a later stage

    Gareth

  3. #3
    Join Date
    Sep 2005
    Posts
    3
    Thanks,

    I am more or less looking for a better way of converting the month of cash from local currency to USD. This USD ammount is is used in many other queries and reports throughout the application, and currently is not 'stored' anywhere (its all in a calculation query).

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Ok "J"
    Just a thought. If you are dealing with transactions like this it may be wise to store the exchange rate against a date ie 23/12/05 $1.49

    this will then allow for you to convert the currency and store the transactions. a historical look up may later be required??

    If your converting through refer to the table and place a set of currency text boxes on the active forms that way youll just update the table as and when

    g

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    gareth
    not sure that storing a single forec rate for a day will work, some currencies are fixed for a day / week / month, some a floating. So I'd suggest you either store the forex rate for that transaction or record timestamps for the transaction(s) OR store the forex value as well as the local value. If data storage requirements are not significant then usually I'd go down the second route.

    The main reason: it simplifies reporting, its a straightforward view of the data (it makes deploying applications easier to users (they don't have to use a SQL join to rerieve the forex value). It gives them less scope to introduce rounding errors in their reports. its amazing how many times you can chase down errors in conversions. the sum of (localval*forex) rate isn't necccesarily the same as sum(localval)*forex. To some beancounters the rounding errors are significant.

  6. #6
    Join Date
    Sep 2005
    Posts
    3
    Thanks Guys,

    Healdem,

    Is there some sort of code that i can use for when I create a record, to pull the correct forex rate into a field in the table that holds the record. That way, I can just perform a simple query rather than a join query, as you suggested?

    I do not no much visual basic or sql, so anything help is much appreciated.

    Thanks again.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Where are you getting your forex rate from?
    As to where you store it, add column in your relevant table make changes to all forms & reports & queries.

    There are 2 strategies either
    store a rate
    or
    store the local currency values as well as your base currency values.

    both break the normalisation rules
    It depends entirely on your preferences.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •