为什么我的计数字段不起作用？ [英] Why doesn't my count field work?

问题描述

我正在尝试计算特定客户在给定日期范围内每个月的表中记录数。我创建了样本数据，以便我可以测试查询，样本数据的日期为6/10 / 11,6 / 11 / 11,7 / 11/11，7/15 / 11,7 / 31/11 ，8/18 / 11,8 / 20/11和8/25/11。换句话说，6月2日，7月3日，8月3日。我的问题是每个文件的计数为1。这是我的SQL代码：

I''m trying to count the number of records in a table for each individual month, for a certain customer, in a given date range. I''ve created sample data so that I can test the query and the sample data has the dates 6/10/11, 6/20/11, 7/1/11, 7/15/11, 7/31/11, 8/18/11, 8/20/11, and 8/25/11. In other words, 2 in June, 3 in July, and 3 in August. My problem is that the count is being 1 for every file. Here is my SQL code:

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

推荐答案

您在[分组依据]中的[EffectiveDate]将导致单一计数因为你不算几个月，而是几天。


我也不会只使用Month（）函数，而且也像Year那样：Format（[EffectiveDate]，& ; yyyymm"）

以获得正确的GroupBy。


我想已添加[EffectiveDate]以区分不同的记录。所以使用YearMonth GroupBy无法解决您对单个记录的需求。

最好为每条记录设置一个序列号。

为此你可以使用Dcount（）具有where子句的函数限制特定月份和客户的计数，例如：

Your [EffectiveDate] in the "Group by" will cause the "single count" as you''re not counting months, but days.

I also wouldn''t use only the Month() function, but also Year like: Format([EffectiveDate],"yyyymm")
to get the correct GroupBy.

I guess the [EffectiveDate] has been added to be able to distinguish the different records. So using a "YearMonth" GroupBy wouldn''t solve your need for the individual records.
It would be better to have a sequencenumber for each record.
For that you can use the Dcount() function with a where clause limiting the count for the specific month and the customer like:

展开 | 选择 | Wrap | 行号

我会试试这个周二我回去工作。谢谢。


我以为我不需要year（）函数，因为我将选择1/1 / xx到6/30 / xx并且7/1 / xx至12/31 / xx。我永远不会越过年底。你认为我还应该使用吗？

I''ll try this on Tuesday when I get back to work. Thanks.

I was thinking that I didn''t need the year() function because I''m going to be selecting 1/1/xx to 6/30/xx and 7/1/xx to 12/31/xx. I''ll never be crossing the year end. Do you think that I should still use it?

这取决于你对Seth的意思。当你搬到2011年时，2010年表格中的数据会发生什么变化。如果它没有被清除或过滤掉，则需要处理。

That depends on what you mean by that Seth. What happens to the data in the table for year 2010 when you move along to 2011. If it''s not cleared or filtered out then it needs to be handled.

这篇关于为什么我的计数字段不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！