SQL - Query count not working

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.
--
Build a little, test a little.


"QB" wrote:

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

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

"QB" wrote:

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

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


"Clifford Bass" wrote in message
...
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

"QB" wrote:

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

"vanderghast" wrote:

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

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


"Clifford Bass" wrote in message
...
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

"vanderghast" wrote:

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,

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

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

Hi,

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

Clifford Bass

"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

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

Clifford Bass wrote:
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