Show 0 for no record in field

[Danny2024]

Hi

I want to show zero against a date if it has no record in the query

For example I spend money on the first 5 days of the month but then save for the rest of the month and spend no further money, I would want to see the amount i spent against those days but then 0 against the remaining dates in the month

I have a table and a query, table has every date for the next 10 years in and the query has the data showing what cost I have spent for days with a record

The SQL is below

SELECT Tbl_Dates.Dates, Qry_Cells_CON_KPI_Daily_Cost.Value


FROM Tbl_Dates INNER JOIN Qry_Cells_CON_KPI_Daily_Cost ON Tbl_Dates.Dates = Qry_Cells_CON_KPI_Daily_Cost.Date_Raised
GROUP BY Tbl_Dates.Dates, Qry_Cells_CON_KPI_Daily_Cost.Value;

I know it is something simple that I have to put in the code above but cant figure it out lol

Thanks in advance

[Minty]

Assuming that daily cost is the figure you are looking for then replace this line with this using the Nz() function

SELECT Tbl_Dates.Dates, Nz(Qry_Cells_CON_KPI_Daily_Cost.Value,0) 

However you are also grouping this data without doing any aggregate (Sum, count, etc) functions, which seems a bit strange...
Also Value is a reserved word in Access so not a great choice of field name - Amount or CostValue would better, as that will trip you up later.

[JoeM]

Try this:

SELECT Tbl_Dates.Dates, Nz(Qry_Cells_CON_KPI_Daily_Cost.Value,0)+0
FROM Tbl_Dates LEFT JOIN Qry_Cells_CON_KPI_Daily_Cost ON Tbl_Dates.Dates = Qry_Cells_CON_KPI_Daily_Cost.Date_Raised
GROUP BY Tbl_Dates.Dates, Nz(Qry_Cells_CON_KPI_Daily_Cost.Value,0)+0;

[JoeM]

However you are also grouping this data without doing any aggregate (Sum, count, etc) functions, which seems a bit strange...
Also Value is a reserved word in Access so not a great choice of field name - Amount or CostValue would better, as that will trip you up later.

Minty,
They will also need to change their Join type to a LEFT JOIN, instead of an INNER JOIN, like I showed above.

[Danny2024]

Thankyou guys for reply

Used JoeM example and worked spot on

didnt realize i had group by on so have removed and changed name as advised, thanks Minty

[JoeM]

You are welcome. Glad it all worked out for you.

Yes, I thought the Group By bit was a bit odd, unless you had duplicate records in your table that you were trying to get out of the results (or if you were trying to Sum by day, in which case you would group on date and SUM the amount field).