Query to Show Zero even when the Count is Zero

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

I have a database that keeps the record of certain trucks that have
committed some offences. A typical record will contain the following:

InDate, V(ehicle)R(egistration)N(umber), Mat(erial the truck is carrying),
etc.

I have a query that grabs the statistics from this table which is as
follows:

SELECT FORMAT(InDate, "yyyy/ww"), COUNT(*) FROM tbl GROUP BY FORMAT(InDate,
"yyyy/ww");

The query is OK when there are offences every week, so that there won't be
discontinuity of the week number.

However, when there is indeed a week that has no offences, that week number
is skipped. When I export the query to excel and plot a chart, I have to
manually add that week with zero offence back, and this is troublesome.

Is there any way to get Access to include a zero count for the skipped week
instead??

Thanks.

Herbert
 
Herbert Chan said:
Hello,

I have a database that keeps the record of certain trucks that have
committed some offences. A typical record will contain the following:

InDate, V(ehicle)R(egistration)N(umber), Mat(erial the truck is carrying),
etc.

I have a query that grabs the statistics from this table which is as
follows:

SELECT FORMAT(InDate, "yyyy/ww"), COUNT(*) FROM tbl GROUP BY FORMAT(InDate,
"yyyy/ww");

The query is OK when there are offences every week, so that there won't be
discontinuity of the week number.

However, when there is indeed a week that has no offences, that week number
is skipped. When I export the query to excel and plot a chart, I have to
manually add that week with zero offence back, and this is troublesome.

Is there any way to get Access to include a zero count for the skipped week
instead??

Thanks.

Herbert
Click to expand...
Hi
Mybee the NZ function can solve the problem, try:

SELECT FORMAT(NZ(InDate, "yyyy/ww")), COUNT(*) FROM tbl GROUP BY
FORMAT(InDate,
"yyyy/ww");

or something like this:

SELECT FORMAT(NZ(InDate, "yyyy/ww")), COUNT(*) FROM tbl GROUP BY
FORMAT(NZ(InDate,
"yyyy/ww";0);


Sigurd
 
Sorry: if there isn't data, a query can't report on it.

One thing you can try is creating a table of weeks, and using a RIGHT JOIN
(or LEFT JOIN, depending what order you put the tables in the SQL) so that
you get at least one row for each week.
 
Back
Top