在MS Access中选择一个范围内的所有日期 [英] Select All Dates in a Range in MS Access

问题描述

我在MS Access 2013数据库中有以下查询，该查询选择最近7天内具有日期的所有记录的计数:

I have the following query in a MS Access 2013 DB that SELECTS a count of all records which have dates within the last 7 days:

SELECT DateValue([Proofpoint Attachment Defense.Received]) AS [Date],
       Count([Proofpoint Attachment Defense.Received]) AS [count]
FROM [Proofpoint Attachment Defense]
WHERE ((([Proofpoint Attachment Defense.Received])>=DateAdd("d",-7,Date())))
GROUP BY DateValue([Proofpoint Attachment Defense.Received])

此查询产生以下结果:

Date        count
10/19/2017    3
10/20/2017    2
10/25/2017    3

即使没有结果，我也需要包括所有7个日期的结果:

The result I need have to include all 7 dates even if there are no results:

Date        count
10/19/2017    3
10/20/2017    2
10/21/2017    0
10/22/2017    0
10/23/2017    0
10/24/2017    0
10/25/2017    3

推荐答案

创建查询或表格以生成覆盖您整个范围的日期，并应用与现在相同的过滤器.

Create a query or table that produces dates covering your entire range and apply the same filter as now.

然后创建一个新查询，以表/查询为源，并在日期字段上方对您的查询进行左外部联接.

Then create a new query with table/query as source and with an left outer join to your query above on the field Date.

这将返回您的计数，现在为空，缺少的日期为Null.如果您希望Null为零，请使用Nz(CountField, 0).

That will return your count as now and Null for the missing dates. Use Nz(CountField, 0) if you want zeroes for Null.

附录:

可以在此处

这篇关于在MS Access中选择一个范围内的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！