Query to Show Zero even when the Count is Zero

[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

 

[Sigurd Bruteig]

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

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

 

[Douglas J. Steele]

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.