How can a Query return a Count of 0? 2

[StarScream]

In a query (design view), I want to count how many times a specific field equals "Teacher" in a table. If there are no records with "Teacher", I want it to return a 0.

TABLE
[Country] [Name] [Job]
Canada John Teacher
Canada Pete Plumber
Canada Sara Teacher
Russia Ivan Singer
Russia Nicolae Electrician


QUERY
[Country] [Job] [Job]
Group By Group By Count
Sort Ascending ="Teacher"


RESULT
Canada Teacher 2

But I want it to return

Canada Teacher 2
Russia Teacher 0

I tried using NZ and the IIF(IsNull) and even DCOUNT.

Any ideas?

 

[boriska40]

Hi,

SELECT Country, "Teacher" AS Spec, Sum(IIf([Job]="Teacher",1,0)) AS CountJob
FROM Table1
GROUP BY Country, "Teacher"

 

[Golom]

"boriska40"'s solution works for "Teacher". To get one that works for all jobs, you need two queries. The first one builds a table of all country/Job combinations like this:

SELECT DISTINCT A.Country, B.Job
FROM CountryJobs AS A, CountryJobs AS B;

Save this As a query named "JC"

Then

SELECT J.Country, J.Job, Count(C.Job) AS [Number]
FROM JC AS J LEFT JOIN CountryJobs AS C ON (J.Country = C.Country) AND (J.Job = C.Job)
GROUP BY J.Country, J.Job;

"CountryJob" is your original table.

 

[boriska40]

Golom,
you are right. But lets do it using 1 query only:

SELECT DISTINCT A.Country, B.Job, (select Count([Job]) from [CountryJob] WHERE [CountryJob].[Country] = A.Country And [CountryJob].[Job] = B.job) AS MyCount
FROM CountryJob AS A, CountryJob AS B
ORDER BY A.Country, B.Job;

 

[Golom]

boriska40

Yep ... that's even better.

 

[StarScream]

Golom, boriska40,

I understand your logic, but what are the "A."s, "B."s, "J."s ? Also, where do I put the SELECT statements? Instead of using DesignView I should use the SQL view, right?

My overall goal is this report which uses the query information. I thought about having the results of 2 different queries provide the info I need. This also would solve my problem, but not sure how to get the report to use the info from both. I'm sure you know of a way.... I'm all ears !

Thanks.

 

[Golom]

The "A" and "B" from boriska40's code are table aliases. Refer to his code fragment "... CountryJob AS A, CountryJob AS B ...". Those mean that we have two instances of the table "CountryJob". One is refered to as "A", the other as "B". A reference (for example) to "A.Country" refers to the "Country" field in the first instance of the table and "B.Job" refers to the Job field in the second instance. Because I used two separate queries I also had a "J" alias that is not needed in boriska40's code.

Yes ... you should use the SQL view. You can type all this stuff in design view but I find SQL view easier because you can see everything. Just paste boriska40's code into SQL view and then change table and field names to the correct ones in your system.

With boriska40's solution you don't need two queries but, if you want to go the two query route then just reference the second of the two in my example and it will automatically run the first one and use its results.

"Where do you put the select?" ... Not sure what you mean. In either solution the select is already there. Just copy and paste the code in SQL view of your query.

 

[boriska40]

Thanks for stars. I think, my star should go to Golom,
because he actually showed the right way.
StarScream, if your question came from real system needs,
could you test time reaction for both our solutions and let us know? Thank you and Golom for your time.

 

[Golom]

To boriska40

You're welcome ... and I still like yours better.