在Access 2013中查找到今天的最接近日期 [英] Finding nearest date to today in Access 2013
本文介绍了在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中查找到今天的最接近日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文