Graph won't display Zeroes

[David]

My report has a bar-graph of items per month for the past year.

In months where zero items were produced, I want to show zero
but the chart skips the zero months.

How can I make every month show? All 12 months even where production
was zero.

 

[Duane Hookom]

You have to get all the months to display in the Row Source query of the
graph. This is usually accomplished with a left or righ join query.

 

[David]

Can you give me a few pointers on how?

You have to get all the months to display in the Row Source query of the
graph. This is usually accomplished with a left or righ join query.

 

[Duane Hookom]

Can you provide your current Row Source and some information about your
tables/fields/data?

 

[David]

My "info" table has fields for Supervisor, date, r1,r2,r3.
My row source query shows the monthly sum of the quantities(r1, r2 &
r3).Row/Source
Month-Year Prod
2007-Jun
2007-Oct 50
2007-Nov 100
2008-Mar 97
2008-Apr 100
2008-May 100
2008-Jun 100
The graph shows a 12 month bar chart where x axis = month, y = sum of
r1+r2+r3.
The graph looks great for supervisors who have production every month.
You can see that months were skipped in the data above. There was no
production in those months.
(June was a "zero" month but it showed up. I want all months to show
on the x-axis

thank you for taking a second look.

dp

SQL of my Row/Source

SELECT Format([Date_Performed],'yyyy') & "-" &
Format([Date_Performed],'mmm') AS [Month-Year], Format((-(100-
((Count([Report_Num])-Sum([No_Issues]))/
Count([Report_Num])*100))),"#") AS Success
FROM [tblPRDAta_in-H]
GROUP BY Format([Date_Performed],'yyyy') & "-" &
Format([Date_Performed],'mmm'), [tblPRDAta_in-H].Station_Name
HAVING ((([tblPRDAta_in-H].Station_Name)=[Forms]![frm_HPRC_filter]!
[Station_Name_Selected]) AND ((Min([tblPRDAta_in-H].Date_Performed))
Between [Forms]![frm_HPRC_filter]![txtStartDate] And [Forms]!
[frm_HPRC_filter]![txtEndDate]))
ORDER BY Min([tblPRDAta_in-H].Date_Performed);

 

[Duane Hookom]

You neet either a table or query with all possible months that you can join
to your current row source. Set the join type to include all records from the
"all possible months" table or query.

--
Duane Hookom
Microsoft Access MVP

My "info" table has fields for Supervisor, date, r1,r2,r3.
My row source query shows the monthly sum of the quantities(r1, r2 &
r3).Row/Source
Month-Year Prod
2007-Jun
2007-Oct 50
2007-Nov 100
2008-Mar 97
2008-Apr 100
2008-May 100
2008-Jun 100
The graph shows a 12 month bar chart where x axis = month, y = sum of
r1+r2+r3.
The graph looks great for supervisors who have production every month.
You can see that months were skipped in the data above. There was no
production in those months.
(June was a "zero" month but it showed up. I want all months to show
on the x-axis

thank you for taking a second look.

dp

SQL of my Row/Source

SELECT Format([Date_Performed],'yyyy') & "-" &
Format([Date_Performed],'mmm') AS [Month-Year], Format((-(100-
((Count([Report_Num])-Sum([No_Issues]))/
Count([Report_Num])*100))),"#") AS Success
FROM [tblPRDAta_in-H]
GROUP BY Format([Date_Performed],'yyyy') & "-" &
Format([Date_Performed],'mmm'), [tblPRDAta_in-H].Station_Name
HAVING ((([tblPRDAta_in-H].Station_Name)=[Forms]![frm_HPRC_filter]!
[Station_Name_Selected]) AND ((Min([tblPRDAta_in-H].Date_Performed))
Between [Forms]![frm_HPRC_filter]![txtStartDate] And [Forms]!
[frm_HPRC_filter]![txtEndDate]))
ORDER BY Min([tblPRDAta_in-H].Date_Performed);

Can you provide your current Row Source and some information about your
tables/fields/data?
--
Duane Hookom
Microsoft Access MVP










- Show quoted text -