SQL - MS ACCESS - Show Cells with NULL value with 0

Question

Hi, I tried to write a query in access.

My aim is;

To get results of how many surgeries are done in one day.

Problem is;

Result are giving me dates and how many surgeries are done but days without any surgery are not listed on the result table.

I want days without surgery to be shown as 0. But there is no record about SURGERY TYPE 1 ON 03.01.2009. I just want to pass that row like;

TYPE 1------------30.01.2009------------0

IS IT POSSIBLE or HOW ?

Eg,

SURGERY TYPES ------------DATES----------------AMOUNT

-------TYPE 1-----------------01.01.2009------------------20

-------TYPE 1-----------------02.01.2009------------------30

--!!--!!-- 03.01.2009 is not shown as 0 ( It didn't appear on the resulting table )--!!--!!--

-------TYPE 1-----------------02.01.2009------------------10

I've tried to use ISNULL function but couldn't get a result.

*B is SURGERY TYPE
*T is DATES
SQL Code

SELECT T1.B, T1.T, Count(T1.T) AS Amount
FROM T1
GROUP BY T1.B, T1.T
HAVING (((T1.B) In (SELECT [B] FROM [T1] As Tmp GROUP BY [B] HAVING Count(*)>1))) ORDER BY T1.B;

Answer 1

You need an outer join. An outer join will allow all records from the main table, and only those records from the outer-joined table that match. This allows records to exist where count is zero.