SQL - Query count not working

Hi John,

That thought occurred to me also, but then
qry_rpt_RespectDateLivraison01 is on the right side of the join, therefore
its columns will be all null in those instances where there truely are no
matching records on the right for the specific type/respect date.

Clifford Bass

"John Spencer" wrote:

I guess I would need to check and see if the values were actually null or if
they were a zero-length string.

The SQL for qry_rpt_RespectDateLivraison01 is not available to examine (unless
I missed seeing it).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

But can you remove the count, and replace the GROUP BY with an ORDER BY to
SEE if REALLY there are only NULLS for the groups with problem? Maybe you
will be surprised to see NOT NULL data, after all, and you will be able to
see it because it won't be aggregated, yet. It is just a matter of
confirmation of your assumption, and you are the only one who can do it, I
mean, we cannot.

Vanderghast, Access MVP

"Clifford Bass" wrote in message
...
Hi John,

That thought occurred to me also, but then
qry_rpt_RespectDateLivraison01 is on the right side of the join, therefore
its columns will be all null in those instances where there truely are no
matching records on the right for the specific type/respect date.

Clifford Bass

"John Spencer" wrote:

I guess I would need to check and see if the values were actually null or
if
they were a zero-length string.

The SQL for qry_rpt_RespectDateLivraison01 is not available to examine
(unless
I missed seeing it).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

Well, not me--I cannot do it; but QB, who is very quiet, can. I trust
that is what you meant.

I do further notice, that he is counting the date on the right side of
the join, which is also part of the join. I do not suppose that that would
make any difference?

Clifford Bass

"vanderghast" wrote:

But can you remove the count, and replace the GROUP BY with an ORDER BY to
SEE if REALLY there are only NULLS for the groups with problem? Maybe you
will be surprised to see NOT NULL data, after all, and you will be able to
see it because it won't be aggregated, yet. It is just a matter of
confirmation of your assumption, and you are the only one who can do it, I
mean, we cannot.

Vanderghast, Access MVP

The query counts on a field of the unpreserved side of the outer join, which
is, technically, the good way to get a count of 0 (if there are only NULLs,
that is, for that group, over that field). A COUNT(*) cannot produces 0 in
a standard GROUP BY query. And since the field is used in the ON clause, it
can only be null in cases where there is no match (an original null will not
match anything, anyhow). So, if the count is one, it is quite probably
because there is a match, maybe the AND involved in the join behave in an
unexpected way for the OP. We only have the initial claim, and no data
sample to reproduce the 'problem'.

Vanderghast, Access MVP


"Clifford Bass" wrote in message
...
Hi,

Well, not me--I cannot do it; but QB, who is very quiet, can. I trust
that is what you meant.

I do further notice, that he is counting the date on the right side of
the join, which is also part of the join. I do not suppose that that
would
make any difference?

Clifford Bass

"vanderghast" wrote:

But can you remove the count, and replace the GROUP BY with an ORDER BY
to
SEE if REALLY there are only NULLS for the groups with problem? Maybe you
will be surprised to see NOT NULL data, after all, and you will be able
to
see it because it won't be aggregated, yet. It is just a matter of
confirmation of your assumption, and you are the only one who can do it,
I
mean, we cannot.

Vanderghast, Access MVP

I'm sorry for not replying sooner, I got pulled off of this and put onto
another project. I really appreciate the help and am back on the case (with
all your help)!

Below is a sample output from qry_rpt_RespectDateLivraison01, the query upon
which the problematic count query is based.
[Type] [Status] [No] [Start] [End] [Respect Date de Livraison]
B Fermé 08E166 06-Feb-2009 1/14/2009 En Avance
B Fermé 08E161 27-Feb-2009 1/20/2009 En Avance
A Fermé 09E020 24-Feb-2009 2/24/2009 À temps
A Fermé 09E706 20-Feb-2009 2/27/2009 En retard
C Fermé 09E021 27-Mar-2009 3/17/2009 En Avance
A Fermé 09E028 10-Apr-2009 4/14/2009 En retard
A Fermé 09E031 20-Apr-2009 4/23/2009 En retard
A Fermé 09E042 19-May-2009 5/19/2009 À temps
A Fermé 09E045 25-May-2009 5/27/2009 En retard

Based on this, using the qry that I initial started this post on, I get the
following results
[Type] [Respect] [CountOfRespect Date de Livraison]
A À temps 2
A En Avance 1
A En retard 4
B À temps 1
B En Avance 2
B En retard 1
C À temps 1
C En Avance 1
C En retard 1

By the Initial data, I should actually be getting
[Type] [Respect] [CountOfRespect Date de Livraison]
A À temps 2
A En Avance 0
A En retard 4
B À temps 0
B En Avance 2
B En retard 0
C À temps 0
C En Avance 1
C En retard 0

Thank you once again!!!

QB


"Clifford Bass" wrote:

Hi,

Well, not me--I cannot do it; but QB, who is very quiet, can. I trust
that is what you meant.

I do further notice, that he is counting the date on the right side of
the join, which is also part of the join. I do not suppose that that would
make any difference?

Clifford Bass

"vanderghast" wrote:

But can you remove the count, and replace the GROUP BY with an ORDER BY to
SEE if REALLY there are only NULLS for the groups with problem? Maybe you
will be surprised to see NOT NULL data, after all, and you will be able to
see it because it won't be aggregated, yet. It is just a matter of
confirmation of your assumption, and you are the only one who can do it, I
mean, we cannot.

Vanderghast, Access MVP

I switched the query to ORDER BY, as you suggested and you are right it does
indeed appear, and yet it is not in the base query, so somehow in my query it
get `created`?!

At least I know that the query is returning the 'proper values', which mean
my query, sql, is faulty in some manner!

All I want is a very simple count of the element from the base query, what
have I done wrong?

QB




"vanderghast" wrote:

I don't have the data the OP uses, but if it was my problem, I would remove
the COUNT and replace the GROUP BY with an ORDER BY to see if what "I
ASSUMED" (as the OP does, when he ****assumes*** the count ***should*** be
zero) is indeed what the data really ***says***. Since the COUNT could then
be done 'by hand', at that point, the nature of the problem should then be
evident (is the OP assumption wrong? is the query logic wrong? or is is an
unknown SQL-bug?) My experience is that user assumptions are more often than
otherwise the problem.


Vanderghast, Access MVP

"Clifford Bass" wrote in message
...
Hi,

I may not be viewing the statement correctly, but it looks to me like
QB is counting the rows on the right side of a left join. And when there
are
no rows on the right side (i.e. one row only, with data on the left side
and
nulls on the right), QB none-the-less is getting a count of 1. So that
would
not involve duplicate rows.

Clifford Bass

"vanderghast" wrote:

I assume the join creates unwanted 'duplicated' records (in the result)
and
while the COUNT(field) does not count the null for the field, in the
result,
the COUNT is none the less wrong.


Vanderghast, Access MVP

Solved! Not understood, but solved!

I switch my count onto another field, instead on the join field and I get 0.
I never thought I`d be so happy to see a 0. :-)

Thank you all! If someone can actually explain my error in plain english, I
would love to understand, so I might avoid this error in the future.

Thank you so very much for the help and advice.

QB





"QB" wrote:

I'm sorry for not replying sooner, I got pulled off of this and put onto
another project. I really appreciate the help and am back on the case (with
all your help)!

Below is a sample output from qry_rpt_RespectDateLivraison01, the query upon
which the problematic count query is based.
[Type] [Status] [No] [Start] [End] [Respect Date de Livraison]
B Fermé 08E166 06-Feb-2009 1/14/2009 En Avance
B Fermé 08E161 27-Feb-2009 1/20/2009 En Avance
A Fermé 09E020 24-Feb-2009 2/24/2009 À temps
A Fermé 09E706 20-Feb-2009 2/27/2009 En retard
C Fermé 09E021 27-Mar-2009 3/17/2009 En Avance
A Fermé 09E028 10-Apr-2009 4/14/2009 En retard
A Fermé 09E031 20-Apr-2009 4/23/2009 En retard
A Fermé 09E042 19-May-2009 5/19/2009 À temps
A Fermé 09E045 25-May-2009 5/27/2009 En retard

Based on this, using the qry that I initial started this post on, I get the
following results
[Type] [Respect] [CountOfRespect Date de Livraison]
A À temps 2
A En Avance 1
A En retard 4
B À temps 1
B En Avance 2
B En retard 1
C À temps 1
C En Avance 1
C En retard 1

By the Initial data, I should actually be getting
[Type] [Respect] [CountOfRespect Date de Livraison]
A À temps 2
A En Avance 0
A En retard 4
B À temps 0
B En Avance 2
B En retard 0
C À temps 0
C En Avance 1
C En retard 0

Thank you once again!!!

QB


"Clifford Bass" wrote:

Hi,

Well, not me--I cannot do it; but QB, who is very quiet, can. I trust
that is what you meant.

I do further notice, that he is counting the date on the right side of
the join, which is also part of the join. I do not suppose that that would
make any difference?

Clifford Bass

"vanderghast" wrote:

But can you remove the count, and replace the GROUP BY with an ORDER BY to
SEE if REALLY there are only NULLS for the groups with problem? Maybe you
will be surprised to see NOT NULL data, after all, and you will be able to
see it because it won't be aggregated, yet. It is just a matter of
confirmation of your assumption, and you are the only one who can do it, I
mean, we cannot.

Vanderghast, Access MVP

Hi QB,

Given the data you have presented, your original query gives me the
correct results in both Access 2007 and 2003. Maybe there is some corruption
in your database. You could do a backup and do a compact and repair. See if
that makes a difference.

Clifford Bass

"QB" wrote:

I'm sorry for not replying sooner, I got pulled off of this and put onto
another project. I really appreciate the help and am back on the case (with
all your help)!

Below is a sample output from qry_rpt_RespectDateLivraison01, the query upon
which the problematic count query is based.
[Type] [Status] [No] [Start] [End] [Respect Date de Livraison]
B Fermé 08E166 06-Feb-2009 1/14/2009 En Avance
B Fermé 08E161 27-Feb-2009 1/20/2009 En Avance
A Fermé 09E020 24-Feb-2009 2/24/2009 À temps
A Fermé 09E706 20-Feb-2009 2/27/2009 En retard
C Fermé 09E021 27-Mar-2009 3/17/2009 En Avance
A Fermé 09E028 10-Apr-2009 4/14/2009 En retard
A Fermé 09E031 20-Apr-2009 4/23/2009 En retard
A Fermé 09E042 19-May-2009 5/19/2009 À temps
A Fermé 09E045 25-May-2009 5/27/2009 En retard

Based on this, using the qry that I initial started this post on, I get the
following results
[Type] [Respect] [CountOfRespect Date de Livraison]
A À temps 2
A En Avance 1
A En retard 4
B À temps 1
B En Avance 2
B En retard 1
C À temps 1
C En Avance 1
C En retard 1

By the Initial data, I should actually be getting
[Type] [Respect] [CountOfRespect Date de Livraison]
A À temps 2
A En Avance 0
A En retard 4
B À temps 0
B En Avance 2
B En retard 0
C À temps 0
C En Avance 1
C En retard 0

Thank you once again!!!

QB

The problem is thus probably in qry_rpt_RespectDateLivraison01, not in the
top most query calling it.


Vanderghast, Access MVP


"QB" wrote in message
...
I switched the query to ORDER BY, as you suggested and you are right it
does
indeed appear, and yet it is not in the base query, so somehow in my query
it
get `created`?!

At least I know that the query is returning the 'proper values', which
mean
my query, sql, is faulty in some manner!

All I want is a very simple count of the element from the base query, what
have I done wrong?

QB




"vanderghast" wrote:

I don't have the data the OP uses, but if it was my problem, I would
remove
the COUNT and replace the GROUP BY with an ORDER BY to see if what "I
ASSUMED" (as the OP does, when he ****assumes*** the count ***should***
be
zero) is indeed what the data really ***says***. Since the COUNT could
then
be done 'by hand', at that point, the nature of the problem should then
be
evident (is the OP assumption wrong? is the query logic wrong? or is is
an
unknown SQL-bug?) My experience is that user assumptions are more often
than
otherwise the problem.


Vanderghast, Access MVP

"Clifford Bass" wrote in message
...
Hi,

I may not be viewing the statement correctly, but it looks to me
like
QB is counting the rows on the right side of a left join. And when
there
are
no rows on the right side (i.e. one row only, with data on the left
side
and
nulls on the right), QB none-the-less is getting a count of 1. So that
would
not involve duplicate rows.

Clifford Bass

"vanderghast" wrote:

I assume the join creates unwanted 'duplicated' records (in the
result)
and
while the COUNT(field) does not count the null for the field, in the
result,
the COUNT is none the less wrong.


Vanderghast, Access MVP