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

    Finding most recent item (2002)

    I have a table of brokers linked to a table of CCY (currency) codes and then to a third table listing the CCY code, an effective date and the currency conversion rate. I want to convert an amount column in the brokers table using the 'most recent' exchange rate.
    I hope that this makes some sense? How can I retrieve the most recent exchange rate? i assume I'm looking for the Maximum date? Thanks, Andy.

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

    Re: Finding most recent item (2002)

    1) Create a query based on the third table.
    Add the CCY code and the effective date to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Set the Total option for the effective date column to Max (the one for CCY remains Group By).
    Save this query as qryMaxDate.

    2) Create a new query based on the third table and on qryMaxDate.
    Join them on the CCY code and on the effective date vs MaxOf...
    Add the CCY code and the conversion rate fields to the query grid.
    Save this query as qryConversionRates.

    3) Create a query based on the first two tables and on qryConversionRates, the latter linked on the CCY code.
    Calculate the converted amount as original amount * conversion rate.

    BTW were the replies to <post:=656,345>post 656,345</post:>, <post:=656,594>post 656,594</post:>, <post:=656,744>post 656,744</post:>, <post:=656,758>post 656,758</post:> and <post:=656,796>post 656,796</post:> helpful?

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

    Re: Finding most recent item (2002)

    Yes all replies were helpful, and always are. I do try to thank people for assistance, but I have to post questions during breaks at work and don't always have time to get back to them. Always appreciated though. 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
  •