Where count = 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have the followinq query that counts the AHT Value if the value is
<= the target. That seems to work fine, however, I need the query to return a
zero value where there are no values that meet the target. how do i do this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm")) AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" & [forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
 
Use sum instead, something like (in design view):

AHTCount: SUM( IIF (AvgAht.Aht)<=[TArgets].[AhtTArget], 1,0))
 
Try changing the join type to an outer join and include
[TArgets].[AhtTArget] is Null in the where clause

Something like the following.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht RIGHT JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
Format([AvgAht].[Date],"yyyymm")=Format([Targets].[Date],"yyyymm") AND
(AvgAht.Aht<=[TArgets].[AhtTArget] OR <=[TArgets].[AhtTArget] is Null )AND
AvgAht.Supervisor Like "*" & [forms]![CCEfficiency]![Supervisor] AND
AvgAht.Date Like "*" & [Forms]![CCEfficiency]![Date] AND
AvgAht.Region Like "*" & [Forms]![CCEfficiency]![Region] AND
AvgAht.JobTitle Like "*" & [forms]![CCEfficiency]![Combo12]

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,
Targets.AHTTarget
 
thanks for your response David, I get an error "function contains wrong
number of arguments"

David Cox said:
Use sum instead, something like (in design view):

AHTCount: SUM( IIF (AvgAht.Aht)<=[TArgets].[AhtTArget], 1,0))


Nero said:
Hello, I have the followinq query that counts the AHT Value if the value
is
<= the target. That seems to work fine, however, I need the query to
return a
zero value where there are no values that meet the target. how do i do
this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm"))
AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" &
[forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
Click to expand...
Click to expand...
 
Thanks for your response John. I tried this, there are no errors, but it
still does not return the 0 values

John Spencer said:
Try changing the join type to an outer join and include
[TArgets].[AhtTArget] is Null in the where clause

Something like the following.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht RIGHT JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
Format([AvgAht].[Date],"yyyymm")=Format([Targets].[Date],"yyyymm") AND
(AvgAht.Aht<=[TArgets].[AhtTArget] OR <=[TArgets].[AhtTArget] is Null )AND
AvgAht.Supervisor Like "*" & [forms]![CCEfficiency]![Supervisor] AND
AvgAht.Date Like "*" & [Forms]![CCEfficiency]![Date] AND
AvgAht.Region Like "*" & [Forms]![CCEfficiency]![Region] AND
AvgAht.JobTitle Like "*" & [forms]![CCEfficiency]![Combo12]

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,
Targets.AHTTarget




Nero said:
Hello, I have the followinq query that counts the AHT Value if the value
is
<= the target. That seems to work fine, however, I need the query to
return a
zero value where there are no values that meet the target. how do i do
this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm"))
AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" &
[forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
Click to expand...
Click to expand...
 
Back to basics.
Which table do you want to return data from that you are not seeing?

Do you want data from Targets that has no records in AvgAHT or Do you want
data from AvgAht that has no records in Targets?



Nero said:
Thanks for your response John. I tried this, there are no errors, but it
still does not return the 0 values

John Spencer said:
Try changing the join type to an outer join and include
[TArgets].[AhtTArget] is Null in the where clause

Something like the following.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht RIGHT JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
Format([AvgAht].[Date],"yyyymm")=Format([Targets].[Date],"yyyymm") AND
(AvgAht.Aht<=[TArgets].[AhtTArget] OR <=[TArgets].[AhtTArget] is
Null )AND
AvgAht.Supervisor Like "*" & [forms]![CCEfficiency]![Supervisor] AND
AvgAht.Date Like "*" & [Forms]![CCEfficiency]![Date] AND
AvgAht.Region Like "*" & [Forms]![CCEfficiency]![Region] AND
AvgAht.JobTitle Like "*" & [forms]![CCEfficiency]![Combo12]

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,





Nero said:
Hello, I have the followinq query that counts the AHT Value if the
value
is
<= the target. That seems to work fine, however, I need the query to
return a
zero value where there are no values that meet the target. how do i do
this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm"))
AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region,
AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" &
[forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
Click to expand...
Click to expand...
Click to expand...
 
:) i want to count the aht value in AvgAHT if it meets/exceeds the ahtTarget
in the targets table.

John Spencer said:
Back to basics.
Which table do you want to return data from that you are not seeing?

Do you want data from Targets that has no records in AvgAHT or Do you want
data from AvgAht that has no records in Targets?



Nero said:
Thanks for your response John. I tried this, there are no errors, but it
still does not return the 0 values

John Spencer said:
Try changing the join type to an outer join and include
[TArgets].[AhtTArget] is Null in the where clause

Something like the following.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht RIGHT JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
Format([AvgAht].[Date],"yyyymm")=Format([Targets].[Date],"yyyymm") AND
(AvgAht.Aht<=[TArgets].[AhtTArget] OR <=[TArgets].[AhtTArget] is
Null )AND
AvgAht.Supervisor Like "*" & [forms]![CCEfficiency]![Supervisor] AND
AvgAht.Date Like "*" & [Forms]![CCEfficiency]![Date] AND
AvgAht.Region Like "*" & [Forms]![CCEfficiency]![Region] AND
AvgAht.JobTitle Like "*" & [forms]![CCEfficiency]![Combo12]

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region, AvgAht.JobTitle,





Hello, I have the followinq query that counts the AHT Value if the
value
is
<= the target. That seems to work fine, however, I need the query to
return a
zero value where there are no values that meet the target. how do i do
this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle = Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm"))
AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region,
AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" &
[forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
Click to expand...
Click to expand...
Click to expand...
 
Hi,


You must use a criteria implying an aggregate (COUNT), in the HAVING clause,
not in the WHERE clause.

COUNT(*) will never be 0... cause if it would be, that means there is ...
no record... , no record as to no "line" to hold, to display that value, in
the first place. So, unless you use a crosstab, COUNT(*)=0 makes no sense.
COUNT(fieldName)= 0 can make sense, on occasions, if the field name is from
an outer join, as example, on the unpreserved side of the join. SO, if you
take John's solution, add

HAVING Count([AvgAht].[Aht]) =0


(and correct the typo in the SELECT clause).



Hoping it may help,
Vanderghast, Access MVP

Nero said:
:) i want to count the aht value in AvgAHT if it meets/exceeds the
ahtTarget
in the targets table.

John Spencer said:
Back to basics.
Which table do you want to return data from that you are not seeing?

Do you want data from Targets that has no records in AvgAHT or Do you
want
data from AvgAht that has no records in Targets?



Nero said:
Thanks for your response John. I tried this, there are no errors, but
it
still does not return the 0 values

:

Try changing the join type to an outer join and include
[TArgets].[AhtTArget] is Null in the where clause

Something like the following.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht RIGHT JOIN Targets ON AvgAht.JobTitle =
Targets.ServiceArea

WHERE
Format([AvgAht].[Date],"yyyymm")=Format([Targets].[Date],"yyyymm") AND
(AvgAht.Aht<=[TArgets].[AhtTArget] OR <=[TArgets].[AhtTArget] is
Null )AND
AvgAht.Supervisor Like "*" & [forms]![CCEfficiency]![Supervisor] AND
AvgAht.Date Like "*" & [Forms]![CCEfficiency]![Date] AND
AvgAht.Region Like "*" & [Forms]![CCEfficiency]![Region] AND
AvgAht.JobTitle Like "*" & [forms]![CCEfficiency]![Combo12]

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region,
AvgAht.JobTitle,





Hello, I have the followinq query that counts the AHT Value if the
value
is
<= the target. That seems to work fine, however, I need the query to
return a
zero value where there are no values that meet the target. how do i
do
this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle =
Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm"))
AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region,
AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" &
[forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
Click to expand...
Click to expand...
Click to expand...
 
Thanks for all the reponses guys, I managed to find a round about solution
for my query.

Michel Walsh said:
Hi,


You must use a criteria implying an aggregate (COUNT), in the HAVING clause,
not in the WHERE clause.

COUNT(*) will never be 0... cause if it would be, that means there is ...
no record... , no record as to no "line" to hold, to display that value, in
the first place. So, unless you use a crosstab, COUNT(*)=0 makes no sense.
COUNT(fieldName)= 0 can make sense, on occasions, if the field name is from
an outer join, as example, on the unpreserved side of the join. SO, if you
take John's solution, add

HAVING Count([AvgAht].[Aht]) =0


(and correct the typo in the SELECT clause).



Hoping it may help,
Vanderghast, Access MVP

Nero said:
:) i want to count the aht value in AvgAHT if it meets/exceeds the
ahtTarget
in the targets table.

John Spencer said:
Back to basics.
Which table do you want to return data from that you are not seeing?

Do you want data from Targets that has no records in AvgAHT or Do you
want
data from AvgAht that has no records in Targets?



Thanks for your response John. I tried this, there are no errors, but
it
still does not return the 0 values

:

Try changing the join type to an outer join and include
[TArgets].[AhtTArget] is Null in the where clause

Something like the following.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht RIGHT JOIN Targets ON AvgAht.JobTitle =
Targets.ServiceArea

WHERE
Format([AvgAht].[Date],"yyyymm")=Format([Targets].[Date],"yyyymm") AND
(AvgAht.Aht<=[TArgets].[AhtTArget] OR <=[TArgets].[AhtTArget] is
Null )AND
AvgAht.Supervisor Like "*" & [forms]![CCEfficiency]![Supervisor] AND
AvgAht.Date Like "*" & [Forms]![CCEfficiency]![Date] AND
AvgAht.Region Like "*" & [Forms]![CCEfficiency]![Region] AND
AvgAht.JobTitle Like "*" & [forms]![CCEfficiency]![Combo12]

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region,
AvgAht.JobTitle,





Hello, I have the followinq query that counts the AHT Value if the
value
is
<= the target. That seems to work fine, however, I need the query to
return a
zero value where there are no values that meet the target. how do i
do
this?
At the moment, if the count equals zero the field is left out.

SELECT
AvgAht.Supervisor,
AvgAht.Date,
AvgAht.Region,
AvgAht.JobTitle,
Count([AvgAht.Aht]) AS AHTCount

FROM AvgAht INNER JOIN Targets ON AvgAht.JobTitle =
Targets.ServiceArea

WHERE
(((Format([AvgAht].[Date],"yyyymm"))=Format([Targets].[Date],"yyyymm"))
AND
((AvgAht.Aht)<=[TArgets].[AhtTArget]))

GROUP BY AvgAht.Supervisor, AvgAht.Date, AvgAht.Region,
AvgAht.JobTitle,
Targets.AHTTarget

HAVING (((AvgAht.Supervisor) Like "*" &
[forms]![CCEfficiency]![Supervisor])
AND ((AvgAht.Date) Like "*" & [Forms]![CCEfficiency]![Date]) AND
((AvgAht.Region) Like "*" & [Forms]![CCEfficiency]![Region]) AND
((AvgAht.JobTitle) Like "*" & [forms]![CCEfficiency]![Combo12]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QueryHelp 4
Null Return on Count Function 4
Counting records and Where criteria 2
Counting occurrances in a chart 5
Show zero count as 0 2
Return 0 for months with no records to count 1
Query from more than one table 3
Struggling to perform IF function in property sheet for table in a 7

Back
Top