|
|
|
date: Sat, 5 Jul 2008 05:51:27 -0700 (PDT),
group: microsoft.public.access.reports
back
Re: Graph won't display Zeroes
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꽯.
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);
On Jul 9, 12:36 pm, Duane Hookom
wrote:
> Can you provide your current Row Source and some information about your
> tables/fields/data?
> --
> Duane Hookom
> Microsoft Access MVP
>
>
>
> "David" wrote:
>
> > Can you give me a few pointers on how?
>
> > On Jul 5, 11:53 am, Duane Hookom
> > wrote:
> > > 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
> > > Microsoft Access MVP
>
> > > "David" wrote:
>
> > > > 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.- Hide quoted text -
>
> - Show quoted text -
date: Fri, 11 Jul 2008 05:10:07 -0700 (PDT)
author: David
Re: Graph won't display Zeroes
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
"David" wrote:
>
> 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);
>
>
>
>
>
> On Jul 9, 12:36 pm, Duane Hookom
> wrote:
> > Can you provide your current Row Source and some information about your
> > tables/fields/data?
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> >
> > "David" wrote:
> >
> > > Can you give me a few pointers on how?
> >
> > > On Jul 5, 11:53 am, Duane Hookom
> > > wrote:
> > > > 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
> > > > Microsoft Access MVP
> >
> > > > "David" wrote:
> >
> > > > > 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.- Hide quoted text -
> >
> > - Show quoted text -
>
>
date: Fri, 11 Jul 2008 08:51:02 -0700
author: Duane Hookom
|
|
