Showing zero values on charts

[Guest]

my current chart shows me data by a given vendor by month if there is no data
for that month the month does not show on the x axis. How can I get all the
months to show on the x axis even if there is no data?

 

[Rob Parker]

Hi Harry,

I suspect that your chart is based on a crosstab query; this will, by
default, only show months for which there is data. You can force all months
to appear by adding an In clause to the SQL statement, so that it ends
something like:
...
PIVOT YourTableName.YourFieldName IN ("Jan", "Feb", "Mar", ...);

You can do this in the query design grid by right-clicking in the top
section (but not on a table) and then selecting Properties to show the
property sheet for the query itself, then entering the headings you want in
the Column Headings field, separated by commas and enclosed in quotes if
they are text strings.

If this doesn't solve your problem, post back, and include the SQL statement
for the Row Source of your chart.

HTH,

Rob

 

[Guest]

I do not have this chart base off a crosstab I have added my SQL statement
below

SELECT (Format([Issue Date],"MMM \'YY")), Count([Problem Code]) AS
[CountOfProblem Code]
FROM [Qry SCAR Report]
GROUP BY (Year([Issue Date])*12 + Month([Issue Date])-1), (Format([Issue
Date],"MMM \'YY"));

I have tried putting the IN statement at the end of this SQL but it keeps
giving me an error.

 

[Rob Parker]

Hi Harry,

Since your chart source is not a crosstab query, the IN clause is not
applicable - it can only be used in a crosstab query.

To solve your problem, you need to introduce all the months (actually, all
the month/year combinations) into your chart's data source query. The way
to do that is to set up a separate table containing all the month/year
combinations of interest, and join that to your existing query, using an
outer join to show all records from the month/year table and only matching
records from the existing query.

There's several ways to set this up; the important point is to normalise the
[Issue Date] field of your existing data to either a single day of the month
(the most convenient would probably be the first day of the month - a
function to do this is available at
http://www.mvps.org/access/datetime/date0007.htm), or to a text string such
as that which you are currently using in your Group By clause, and to set up
your table of all months/years with the same format (either a date/time
field or a text field, respectively). A possible problem with using a text
field is that you cannot sort by month/year on this field; however, if you
enter the month/year data in order in the month/year table, and do not apply
any other sort in your query, you may avoid this problem, however, you would
be better advised to use a separate number to establish the sort order.

Using the text format approach, the steps are:

1. Design a new table, tblMonthsYears, with a text field named MonthYear
and a number field named SortOrder. Add the month/year entries of interest
to this table, in the format you want (which must match the format used in
the next step). Sample entries would be:
MonthYear SortOrder
Jan '07 1
Feb '07 2
Mar '07 3
Apr '07 4
...

2. Add a new field to your existing Qry SCAR Report query to normalise
the [Issue Date] field to this same format. In a blank field in the query
design grid, enter:
MYIssueDate: Format([Issue Date],"mmm"" '""yy")

3. Set the source of your chart to the following, either by creating a
new query with this SQL, or by entering this SQL directly in the Row Source
field of the chart:
SELECT tblMonthsYears.MonthYear, Count([Qry SCAR Report].[Problem Code])
AS [CountOfProblem Code]
FROM tblMonthsYears LEFT JOIN [Qry SCAR Report] ON
tblMonthsYears.MonthYear = [Qry SCAR Report].MYIssueDate
GROUP BY tblMonthsYears.MonthYear, tblMonthsYears.SortOrder
ORDER BY tblMonthsYears.SortOrder;

And that should do it.

I notice that your existing query contains two Group By parameters. I do
not understand what you are attempting to do with the first of these (the
Year([Issue Date])*12 + Month([Issue Date])-1 value); it seems pointless to
me, and while it will give a value different to that produced by the other
Group By parameter (the Format([Issue Date],"MMM \'YY") value), the pair of
values for (for any given [Issue Date]) will be the same, so the overall
effect of the two parameters is the same as the effect of only one. In the
new SQL statement above, the Group By is done on the MonthYear text string
from the new tblMonthsYears, and on the SortOrder value so that it can be
included in the Order By clause.

HTH,

Rob

I do not have this chart base off a crosstab I have added my SQL statement
below

SELECT (Format([Issue Date],"MMM \'YY")), Count([Problem Code]) AS
[CountOfProblem Code]
FROM [Qry SCAR Report]
GROUP BY (Year([Issue Date])*12 + Month([Issue Date])-1), (Format([Issue
Date],"MMM \'YY"));

I have tried putting the IN statement at the end of this SQL but it keeps
giving me an error.

Hi Harry,

I suspect that your chart is based on a crosstab query; this will, by
default, only show months for which there is data. You can force all
months
to appear by adding an In clause to the SQL statement, so that it ends
something like:
...
PIVOT YourTableName.YourFieldName IN ("Jan", "Feb", "Mar", ...);

You can do this in the query design grid by right-clicking in the top
section (but not on a table) and then selecting Properties to show the
property sheet for the query itself, then entering the headings you want
in
the Column Headings field, separated by commas and enclosed in quotes if
they are text strings.

If this doesn't solve your problem, post back, and include the SQL
statement
for the Row Source of your chart.

HTH,

Rob

 

[Guest]

Thanks Rob,
This makes perfect sense...
I never thought of doing this, somethimes you can't see the forest from the
trees.

Hi Harry,

Since your chart source is not a crosstab query, the IN clause is not
applicable - it can only be used in a crosstab query.

To solve your problem, you need to introduce all the months (actually, all
the month/year combinations) into your chart's data source query. The way
to do that is to set up a separate table containing all the month/year
combinations of interest, and join that to your existing query, using an
outer join to show all records from the month/year table and only matching
records from the existing query.

There's several ways to set this up; the important point is to normalise the
[Issue Date] field of your existing data to either a single day of the month
(the most convenient would probably be the first day of the month - a
function to do this is available at
http://www.mvps.org/access/datetime/date0007.htm), or to a text string such
as that which you are currently using in your Group By clause, and to set up
your table of all months/years with the same format (either a date/time
field or a text field, respectively). A possible problem with using a text
field is that you cannot sort by month/year on this field; however, if you
enter the month/year data in order in the month/year table, and do not apply
any other sort in your query, you may avoid this problem, however, you would
be better advised to use a separate number to establish the sort order.

Using the text format approach, the steps are:

1. Design a new table, tblMonthsYears, with a text field named MonthYear
and a number field named SortOrder. Add the month/year entries of interest
to this table, in the format you want (which must match the format used in
the next step). Sample entries would be:
MonthYear SortOrder
Jan '07 1
Feb '07 2
Mar '07 3
Apr '07 4
...

2. Add a new field to your existing Qry SCAR Report query to normalise
the [Issue Date] field to this same format. In a blank field in the query
design grid, enter:
MYIssueDate: Format([Issue Date],"mmm"" '""yy")

3. Set the source of your chart to the following, either by creating a
new query with this SQL, or by entering this SQL directly in the Row Source
field of the chart:
SELECT tblMonthsYears.MonthYear, Count([Qry SCAR Report].[Problem Code])
AS [CountOfProblem Code]
FROM tblMonthsYears LEFT JOIN [Qry SCAR Report] ON
tblMonthsYears.MonthYear = [Qry SCAR Report].MYIssueDate
GROUP BY tblMonthsYears.MonthYear, tblMonthsYears.SortOrder
ORDER BY tblMonthsYears.SortOrder;

And that should do it.

I notice that your existing query contains two Group By parameters. I do
not understand what you are attempting to do with the first of these (the
Year([Issue Date])*12 + Month([Issue Date])-1 value); it seems pointless to
me, and while it will give a value different to that produced by the other
Group By parameter (the Format([Issue Date],"MMM \'YY") value), the pair of
values for (for any given [Issue Date]) will be the same, so the overall
effect of the two parameters is the same as the effect of only one. In the
new SQL statement above, the Group By is done on the MonthYear text string
from the new tblMonthsYears, and on the SortOrder value so that it can be
included in the Order By clause.

HTH,

Rob

I do not have this chart base off a crosstab I have added my SQL statement
below

SELECT (Format([Issue Date],"MMM \'YY")), Count([Problem Code]) AS
[CountOfProblem Code]
FROM [Qry SCAR Report]
GROUP BY (Year([Issue Date])*12 + Month([Issue Date])-1), (Format([Issue
Date],"MMM \'YY"));

I have tried putting the IN statement at the end of this SQL but it keeps
giving me an error.

Hi Harry,

I suspect that your chart is based on a crosstab query; this will, by
default, only show months for which there is data. You can force all
months
to appear by adding an In clause to the SQL statement, so that it ends
something like:
...
PIVOT YourTableName.YourFieldName IN ("Jan", "Feb", "Mar", ...);

You can do this in the query design grid by right-clicking in the top
section (but not on a table) and then selecting Properties to show the
property sheet for the query itself, then entering the headings you want
in
the Column Headings field, separated by commas and enclosed in quotes if
they are text strings.

If this doesn't solve your problem, post back, and include the SQL
statement
for the Row Source of your chart.

HTH,

Rob

my current chart shows me data by a given vendor by month if there is
no
data
for that month the month does not show on the x axis. How can I get
all
the
months to show on the x axis even if there is no data?