SQL - Query count not working

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

Hello,

I have a first query which creates a base list from which I then base a
second query (the one I'm having trouble with) which does a basic count which
is then used to create pie charts from.

The query itself is:

SELECT tbl_RespectdelaLivraison.Type, tbl_RespectdelaLivraison.Respect,
Count(qry_rpt_RespectDateLivraison01.[Respect Date de Livraison]) AS
[CountOfRespect Date de Livraison]
FROM tbl_RespectdelaLivraison LEFT JOIN qry_rpt_RespectDateLivraison01 ON
(tbl_RespectdelaLivraison.Respect = qry_rpt_RespectDateLivraison01.[Respect
Date de Livraison]) AND (tbl_RespectdelaLivraison.Type =
qry_rpt_RespectDateLivraison01.Type)
GROUP BY tbl_RespectdelaLivraison.Type, tbl_RespectdelaLivraison.Respect;

The problem I was having is that I always need to see all the possible
variation combination

Types
A
B
C

RespectDateLivraison01
Before
Ontime
Late

Hence I added the Left join to a table which force them all to appear, and
this appears to work properly.

The Problem:
For an unknown reson the count equals 1 when it should equal 0 for those
variations that are created by the Left Join and not from the 1st query. How
can I resolve this to get the proper count?

Thank you

QB
 
first query which creates a base list
What is the first query actually doing? Post the SQL.
 
Hi,

Try using this instead so that you do not count the "null" rows:

Sum(IIf(IsNull(qry_rpt_RespectDateLivraison01.[Respect Date de Livraison]),
0, 1)) AS
[CountOfRespect Date de Livraison]

Clifford Bass
 
COUNT(fieldName) does NOT count the nulls. It is COUNT(*) which counts the
rows (having or not null in an, then, 'unspecified' field).


Vanderghast, Access MVP
 
Hi,

Thanks for the correction! I am not sure how I did not know that after
all these years!

So what then would you think would be the reason that QB is getting
those null values counted anyway?

Clifford Bass
 
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
 
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
 
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
 
Hi,

Makes sense to me--I have used that method myself for exactly those
reasons.

Clifford Bass
 
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 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
 
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,

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
 
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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
The problem is thus probably in qry_rpt_RespectDateLivraison01, not in the
top most query calling it.


Vanderghast, Access MVP
 
Back
Top