Displaying a zero for a count with no matches in a query 1

[Delboy14]

Access 2002 (sorry I forgot a title the last time)

In a query I count the number of patients from each doctors surgery that fail a certain criteria. This works correclty displaying a count of the patients from each practice who fail. The problem I have is that very few people ever fail this, is there a way to display a zero for each practice who has no patients failing this criteria

the sql for the query is shown below;

SELECT Count(ConsultsQueryLast.PatientIndex) AS NumberOfPatients, ConsultsQueryLast.PracticeID
FROM ConsultsQueryLast
WHERE (ConsultsQueryLast.Drug18 <> &quot;&quotAND ConsultsQueryLast.Drug19 Is Null) AND ConsultsQueryLast.Drug12 Is Null)
GROUP BY ConsultsQueryLast.PracticeID;

 

[tlbroadbent]

Try this.

SELECT a.PracticeID, NZ(b.NumberOfPatients,0) As NumPatients
FROM table As a LEFT JOIN
(SELECT
PracticeID,
Count(PatientIndex) AS NumberOfPatients
FROM ConsultsQueryLast
WHERE Drug18 <> &quot;&quot;
AND Drug19 Is Null
AND Drug12 Is Null
GROUP BY PracticeID) As b
On a.PracticeID=b.PracticeID; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Delboy14]

Sorry but this is still producing a query with no records

 

[Delboy14]

This is exactly what I typed in, I think it was you said?
The only place I thought there could be a problem was line 3 of the code where I selected from the table I was creating is this what you meant?

SELECT a.PracticeID, NZ(b.NumberOfPatients, 0) As NumPatients
FROM MaintenanceSteroidWithoutInhaled as a LEFT JOIN
(SELECT PracticeID, Count(PatientIndex) AS NumberOfPatients
FROM ConsultsQueryLast
WHERE ConsultsQueryLast.Drug18 <> &quot;&quot; AND ConsultsQueryLast.Drug19 Is Null AND ConsultsQueryLast.Drug12 Is Null
GROUP BY PracticeID) as b On a.PracticeID=b.PracticeID;

 

[Delboy14]

I have changed this table to ConsultsQueryLast, which lists the patients last consultation, the result of using this table is a count of the number of patients for each patient(in this case zero) and not each practice, do you know where I am going wrong

 

[Delboy14]

ps. sorry to add this on, but what does NZ() do in the query as I am using this database with Crystal Reports and it does not understand it