Finding nearest date to today in Access 2013

Question

My query returns results similar to this.

id        date
510010    12/09/2013
510010    10/09/2013
510010    05/09/2013
510010    14/09/2013
510012    14/09/2013
510012    10/09/2013

Is there a way that I can make my query only select the date nearest todays date for each distinct id?

Expected outcome.

510010    12/09/2013
510010    10/09/2013
510012    10/09/2013

Thanks, Chris

Answer 1

If you have a table named [YourTable] with the data...

id      date
------  ----------
510010  2013-09-12
510010  2013-09-10
510010  2013-09-05
510010  2013-09-14
510012  2013-09-14
510012  2013-09-10

...then you can create a saved query named [CalculateDaysAway] in Access...

SELECT 
    [id], 
    [date], 
    Abs(DateDiff("d", [date], Date()) AS DaysAway 
FROM YourTable

...which (when run on 2013-09-11) returns

id      date        DaysAway
------  ----------  --------
510010  2013-09-12  1
510010  2013-09-10  1
510010  2013-09-05  6
510010  2013-09-14  3
510012  2013-09-14  3
510012  2013-09-10  1

Now you can use that query as a basis for the query to return the closest date(s)...

SELECT 
    CalculateDaysAway.[id], 
    CalculateDaysAway.[date]
FROM 
    CalculateDaysAway
    INNER JOIN
    (
        SELECT
            [id], 
            MIN(DaysAway) AS MinOfDaysAway
        FROM CalculateDaysAway
        GROUP BY [id]
    ) AS MinDays
        ON CalculateDaysAway.[id] = MinDays.[id]
            AND CalculateDaysAway.DaysAway = MinDays.MinOfDaysAway

...which (when run on 2013-09-11) returns

id      date
------  ----------
510010  2013-09-12
510010  2013-09-10
510012  2013-09-10