MS Access Query problem 1

[Bill4tektips]

I have created a query in Access 2003 to count the number of times an article appears in a field. My problem is that I would like to show "0" if nothing is found and not a blank query. Any help would be appreciated.

 

[combo]

If an empty entry appears, you can use NZ function with the second argument set to 0.

combo

 

[Bill4tektips]

Sorry for my ignorance but what is the NZ function?

 

[SkipVought]

Are you also ingnorant of the HELP feature in Access? You might want to give that a try.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Looking up Nz() won't be of much help regarding this thread. Clearly Bill4tektips would like to display a blank record with a 0 in the count which is not possible in a basic select query without some fancy joins.

Assuming a table of colors:
[tt]
tblColors
[color]
Blue
Green
Orange
Red
White
[/tt]
and a table of our favorite colors:
[tt]
tblOurFavs
[TTer] [FavColor]
Skip Red
DHookom Green
Combo White
Bill Green
[/tt]
I expect Bill4tektips wants to query tblOurFavs for "Blue" and display a count field with a 0. This query will not return any rows:

SELECT Count(tblOurFavs.TTer) AS CountOfTTer, tblOurFavs.FavColor
FROM tblOurFavs
GROUP BY tblOurFavs.FavColor
HAVING tblOurFavs.FavColor="Orange";

This query will return the results I expect are desired using a LEFT JOIN:

SELECT Count(tblOurFavs.TTer) AS CountOfTTer, tblColors.Color
FROM tblColors LEFT JOIN tblOurFavs ON tblColors.Color = tblOurFavs.FavColor
GROUP BY tblColors.Color
HAVING tblColors.Color="Orange";

[tt]
CountOfTTer Color
----------- ------
0 Orange
[/tt]




Duane
Hook'D on Access
MS Access MVP

 

[Bill4tektips]

Thanks dhookam, works OK now

 

[Bill4tektips]

It works OK on "Open" but when I add criteria to see if there are any "Closed" I am just getting a blank query again. My sql is:

This works OK:

SELECT Count(tblfeedback.UKProcessNo) AS CountOfUKProcessNo, tblCycle.CycleName
FROM tblCycle LEFT JOIN tblfeedback ON tblCycle.CycleName = tblfeedback.UKProcessNo
GROUP BY tblCycle.CycleName, tblfeedback.DateofCompletion
HAVING (((tblCycle.CycleName)="CMC 1 Manage Company Strategy") AND ((tblfeedback.DateofCompletion) Is Null));

This doesn't

SELECT Count(tblfeedback.UKProcessNo) AS CountOfUKProcessNo, tblCycle.CycleName, tblfeedback.DateofCompletion
FROM tblCycle LEFT JOIN tblfeedback ON tblCycle.CycleName = tblfeedback.UKProcessNo
GROUP BY tblCycle.CycleName, tblfeedback.DateofCompletion
HAVING (((tblCycle.CycleName)="CLC 1 Win Contract") AND ((tblfeedback.DateofCompletion) Is Not Null));

 

[dhookom]

If you must place a criteria against the "right" table of the left join, you may need to use this query in another query with another tblCycle LEFT JOIN.

Duane
Hook'D on Access
MS Access MVP