Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date dependent exchange rates (Access 2000)

    Hi

    I have a table of data stretching over about 4 years for multiple different currencies and a series of different rates to apply to bring them back in to the home currency of sterling, (on a month by month basis) so I can do this manually if needs be.

    I am hoping that there is some way of matching a date and currency to the relevant rate prevailing at that time i.e. if a date from from my first table is the 14th January 2004 and the ammount in dollars, then that should be calculated against the January 2004 $ rate etc, with the help of you guys and a query ?

    I hope that makes sense and some suggestions would be really usefull.

    regards

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

    Re: Date dependent exchange rates (Access 2000)

    See the attached database. I contains a table tblRates with exchange rates and a date range for each, and a table tblTransactions with a currency, an amount and a transaction date. The query qryResult combines these to look up the exchange rate for the transaction.

  3. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date dependent exchange rates (Access 2000)

    Thanks Hans

    I'll have to download it at home, but your normally spot on so thanks in advance.

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

    Re: Date dependent exchange rates (Access 2000)

    To give you an idea, here is the SQL for the query:

    SELECT tblTransactions.Currency, tblTransactions.Amount, tblTransactions.TransactionDate, tblRates.Rate, [Rate]*[Amount] AS Result
    FROM tblTransactions INNER JOIN tblRates ON tblTransactions.Currency = tblRates.Currency
    WHERE (((tblTransactions.TransactionDate) Between [StartDate] And [EndDate]));

    Screenshots of table and query below.

  5. #5
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date dependent exchange rates (Access 2000)

    Thanks Hans

    I'll give it a go this afternoon, I think I can follow it.

    so thanks again

Posting Permissions

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