Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transposing Exchange Rates (2003)

    I have an Excel worksheet which lists currency codes (EUR, USD, etc.) on the left, has periods across the top (200608, 200609, etc. - representing the year and month) and the exchange rates to convert to GBP in the 'data' area. I want to bring this into Access and use it to look up and convert currencies. That is, my table contains an amount a currency code and a period. How can I look up the exchange rates, or do I need to (somehow) transpose the Excel data? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Exchange Rates (2003)

    Say that your imported Excel table looks like this (data are fictitious)

    <table border=1><td>CurrencyCode</td><td align=right>200607</td><td align=right>200608</td><td align=right>200609</td><td>EUR</td><td align=right>0.67</td><td align=right>0.66</td><td align=right>0.68</td><td>USD</td><td align=right>0.53</td><td align=right>0.54</td><td align=right>0.52</td></table>
    And say that you have a table containing fields Period and CurrencyCode. You can create a query based on this table and add a calculated field

    DLookup([Period], "ExcelTable", "CurrencyCode = " & Chr(34) & [CurrencyCode] & Chr(34))

    This should work OK for a limited number of lookups. However, if you have a lot of records, DLookup is slow. In that case, it would be better to create a new table that looks like this:

    <table border=1><td>CurrencyCode</td><td>Period</td><td>Rate</td><td>EUR</td><td align=right>200607</td><td align=right>0.67</td><td>EUR</td><td align=right>200608</td><td align=right>0.66</td><td>EUR</td><td align=right>200609</td><td align=right>0.68</td><td>USD</td><td align=right>200607</td><td align=right>0.53</td><td>USD</td><td align=right>200608</td><td align=right>0.54</td><td>USD</td><td align=right>200609</td><td align=right>0.52</td></table>
    If you search this forum for Normalize, you'll find several threads explaining how to use code for this. You could also split the period into a year field and a month field, or convert it to a date/time field.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing Exchange Rates (2003)

    Thanks (once again, 50k!). I think the second method is preferable - I don't like using DLOOKUP.

    I can Transpose the data in Excel and tidy it up before importing. A Crosstab query could always recover the look of the original Excel data.. Andy.

Posting Permissions

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