在Access 2013中查找到今天的最接近日期

问题描述：

我的查询返回的结果与此类似.

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

有没有一种方法可以使我的查询仅为每个不同的ID选择与今天最接近的日期?

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

预期结果.

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

谢谢，克里斯

答

如果您有一个名为[YourTable]的表，其中包含数据...

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

...然后您可以在Access中创建一个名为[CalculateDaysAway]的保存的查询...

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

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

...(在2013-09-11上运行时)返回的...

...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

...返回(当运行于2013-09-11时)

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

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

在Access 2013中查找到今天的最接近日期

相关推荐