Return sums even if no data exist in given day

I have a table with amounts only on some days, e.g.:

[DATE] [AMT]
11/1/2017 $123
11/1/2017 $50
11/3/2017 $123

How can I query the data and get:

11/1/2017 $173
11/2/2017 $0
11/3/2017 $123

I tried like:

SELECT Day([Date]) AS [Day], Nz(Sum(AMT),0) AS [Day Total]
FROM mytable
WHERE Month([Date])=11
GROUP BY Day([Date]);

But still it doesn’t return 0s for days without data, any ideas?

Answer

In Access, you can create a series of dates between the first and the last date, and then create an outer join to your summed data.

Here is how to create the date series:

Create a date table between two dates