Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013

    Unanswered: Select record based on the max value of 2 columns

    I have a problem, which I would think is quite simple.
    I have a table with foreign exchange rates posted at different time intervals. What I would like to do is pick the most recent exchange rate for each currency.

    I have tried to create an example of my table below:

    Currency Years Months FX_Value
    USDGBP 2014 11 0.9
    USDGBP 2015 2 0.8
    USDGBP 2015 8 0.85
    USDEUR 2014 11 1.2
    USDEUR 2015 2 1.15
    USDEUR 2015 8 1.1

    I would like the query to return the newest records based on the year and the month column:

    Currency Years Months FX_Value
    USDGBP 2015 8 0.85
    USDEUR 2015 8 1.1

    I have searched the internet for quite some time now without any luck.
    I use MS Access 2010 by the way.

    Hope you can help.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    First you need a Group query to return the last entry for each currency, something like this
    SELECT Currency, Max(Years) as LastYear, Max(Months) as LastMonth FROM TableName GROUP BY Currency
    You then need to INNERR JOIN this back to the table on Currency, year and month respectively (this assumes that there is only one entry per month per year per currency - the PK?). from this second query will then return the last record for each currency ie. the last FX_value.



Posting Permissions

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