No related record to be counted as 0(zero) (1 Viewer)

[jamie_b]

Greetings,

Forgive me for being lazy today. Lack of sleep coupled to age...

One to Many tables:

tbl_Doctors - Main Table : PK is doc_ID
tbl_Patients - Related Table : PK is pat_ID and 'related key' is docpat_ID

Dr. John has 3 Patients
Dr. Sam has 7 Patients
Dr. Andrew has "No Patients" IE. NULL

The (totals)Query:
Group by doc_ID (tbl_Doctors)
Count of pat_ID (tbl_Patients)

It works but ofcourse it does not include Dr. Andrew - as it shouldn't, because there is no related record in tbl_Patients.

What must the WHERE statement be to include Dr Andrews' number of Patients and display it as "0" (zero) ?

Please give me a hint at your convenience.

Thank you kindly.
Jamie.

 

[JHB]

A left join, (change you table name and field name to yours)

SELECT DocTable.DocName, Count(ParTable.ParId) AS CountOfParId
FROM DocTable LEFT JOIN ParTable ON DocTable.DocID = ParTable.DocId
GROUP BY DocTable.DocName;

 

[jamie_b]

A left join, (change you table name and field name to yours)

SELECT DocTable.DocName, Count(ParTable.ParId) AS CountOfParId
FROM DocTable LEFT JOIN ParTable ON DocTable.DocID = ParTable.DocId
GROUP BY DocTable.DocName;

Thank you. The hint I was looking for is "left join".

I did have the Join, but in Join Properties I had Option1 selected - (fields from both tables is the same). Instead of Option 2 (all records from 'related table').

I should have checked that. I'm just having one of those days...

Thanks again.

 

[JHB]

You're welcome - luck with you project.