为什么COUNT会给出错误的结果？如何显示没有数据的句点？ [英] Why does COUNT give the wrong result and how do I display periods without data?

问题描述

嗨！

我有两个问题：一个关于COUNT函数的问题，一个关于如何显示一个月的问题，即使我没有那个月的任何数据。


我有一个Access数据库，我想在两个日期之间创建一个查询来计算列表中的所有内部单位（输入？-table）。我希望我的列表按年和月分组。这是从VBA调用的SQL代码：

展开 | 选择 | Wrap | 行号

解决方案

< blockquote class =post_quotes>
嗨！

我有两个问题：一个关于COUNT函数的问题，一个关于如何显示一个月的问题，即使我没有那个月的任何数据。


我有一个Access数据库，我想在两个日期之间创建一个查询来计算列表中的所有内部单位（输入？-table）。我希望我的列表按年和月分组。这是从VBA调用的SQL代码：


首先COUNT问题：当我使用COUNT函数计算满足我的请求的记录数时，我得到一个答案是真实答案的21倍（这就是为什么我在代码中除以21的原因）。为什么是这样？我得到了正确的答案，但我不喜欢这样做。有没有办法得到正确的结果而不必除以21？


另一个问题：对于表中没有数据的月份，我想显示一个最终表中的零值（而不是空）（？INTU？-table）。我的输入？列表没有得到所有月份的条目，我还没弄明白如何解决这个问题。


我真的很感激如果有人可以帮助我这个！

您的第一个问题：

---------------------- ------

参数声明不应该是你的sql语句的一部分....它是一个单独的声明，它将参数传递给sql语句（正确的语法如下所示）。如果参数包含在sql中，它们将导致代码执行重叠。

展开 | 选择 < span class =codeDivider> | Wrap | 行号

感谢您的回复！不幸的是，无论如何我还没有做对。


第一个问题：

我从SQL查询中删除了参数声明，但是我仍然得到了错误的结果（与之前相同）。这就是我执行查询的方式：

展开 | 选择 | Wrap | 行号

以这种方式执行SQL会发生什么？是的，你可以尝试新西兰的约会。如果NZ不适用于该日期，那么您将必须使用外部联接加入另一个表。

展开 | 选择 | Wrap | 行号

Hi!
I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don?t have any data for that month.

I have an Access database where I want to create a query to count all the internal units in my list (the ?input?-table), between two dates. I want my list to be grouped by year and month. Here is the SQL-code, as called from VBA:

Expand|Select|Wrap|Line Numbers

解决方案

Hi!
I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don?t have any data for that month.

I have an Access database where I want to create a query to count all the internal units in my list (the ?input?-table), between two dates. I want my list to be grouped by year and month. Here is the SQL-code, as called from VBA:

First the COUNT-question: when I use the COUNT-function to count how many records that satisfies my request, I get an answer that is 21 times as high as the real answer (that?s why I have divided by 21 in the code). Why is this? I get the right answer, but I don?t like to do it this way. Is there a way to get the right result without having to divide by 21?

The other question: for the months where there are no data in the table, I would like to display a zero value (instead of nothing) in the final table (the ?INTU?-table). My ?input?-list haven?t got entries for all months, and I haven?t figured out how to solve that.

I would really appreciate if anyone could help me out with this!

Your First Question:
----------------------------
The parameter declaration should not be part of your sql statement....it is a separate declaration that passes parameters to the sql statement (proper syntax is shown below). If parameters are included in the sql, they will cause an overlap in the code execution.

Expand|Select|Wrap|Line Numbers

Thanks for your reply! Unfortunately, I haven?t got it right anyway.

The first question:
I removed the parameter declaration from the SQL query, but I still get the wrong result (same as before). This is how I execute the queries:

Expand|Select|Wrap|Line Numbers

What happens when you execute your SQL this way? And yes, you can try NZ for the date. if NZ does not work for the date, then you will have to join another table, using an outer join.

Expand|Select|Wrap|Line Numbers

这篇关于为什么COUNT会给出错误的结果？如何显示没有数据的句点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！