line graph question

[Jasonm]

Good evening all! I have been working on adding a line graph on a report
that spans several years and data is grouped by year and month. Data is
displayed as Average Monthly Values, and then summarized in the Year group
footer (if that makes any sense!). I wanted the chart to be in the Year
Group footer and ONLY display the data from the year that that footer
represnets.

The graph displays ALL of the data from the full range of the query. Can
this be done? I guess that I was assuming that since I placed the chart in
this footer that it would be like the controls in the footer and only
summarize the data from the year...

Any assistance would be greatly appreciated.

Thanks, Jm

 

[Duane Hookom]

The graph control is somewhat similar to a subreport. You can set the Link
Master/Child properties to synch the years.

 

[Jasonm]

I have tried that, but am getting a bizarre error stating that I "connot
build a lik between unbound forms!"

This doesn't make any sense to me because the fom is bound to a query, and
when I ran the chart wizard it gave me the option to link the fields...
However when I run the report no data is displayed in the chart until I
remove the links between parent and child...

Sorry it took me so long to post back, I ended up working all day, and
didn't get to check back in... I will keep trying a few things, to see what
I can do, but any suggestions would be greatly appreciated!

Thanks! Jm

 

[Duane Hookom]

You don't have to use the "..." button to create the link. Simply type in the
field names.

It's a bit difficult to trouble-shoot report/chart issues without being
privy to your Record Source and Row Source properties as well as the section
of the report containing the chart.

 

[Jasonm]

I appreciate the effort and sympathize about not having enough
information...

The chart data source is as follows:
SELECT (Format([CTDate],"MMM 'YY")),Avg([ClearwellLogRemoval]) AS [Clearwell
Log Removal],Avg([SedBasinLogRemoval]) AS [Sed Basin Log
Removal],Avg([TOTALreduction]) AS [TOTAL Log Removal] FROM
[qryDisinfectionProfile_2] GROUP BY (Year([CTDate])*12 +
Month([CTDate])-1),(Format([CTDate],"MMM 'YY"));

The report has two groups, both on the date field CTDate. On is grouped on
Year and the onther on Month. The data is displayed in the Months header
section and summarized in the Year footer where the chart is placed.

I have tried entering in the CTDate manually in the link parent/link child,
but then NO data displays. I am thinking that it is only looking at one date
possibly when I enter the link parent/child information (?).

Again, any information that you could provide would be very much
appreciated. I can change my query so I am only looking at one year at a
time, but I would rather run it once, and get several years worth of data.

Thanks, Jm

 

[Duane Hookom]

I would think you would want to link on the same value as the group. Try add
a year field/calculation to your Row Source for the chart and link it to a
year value in the Year Footer.
--
Duane Hookom
Microsoft Access MVP

I appreciate the effort and sympathize about not having enough
information...

The chart data source is as follows:
SELECT (Format([CTDate],"MMM 'YY")),Avg([ClearwellLogRemoval]) AS [Clearwell
Log Removal],Avg([SedBasinLogRemoval]) AS [Sed Basin Log
Removal],Avg([TOTALreduction]) AS [TOTAL Log Removal] FROM
[qryDisinfectionProfile_2] GROUP BY (Year([CTDate])*12 +
Month([CTDate])-1),(Format([CTDate],"MMM 'YY"));

The report has two groups, both on the date field CTDate. On is grouped on
Year and the onther on Month. The data is displayed in the Months header
section and summarized in the Year footer where the chart is placed.

I have tried entering in the CTDate manually in the link parent/link child,
but then NO data displays. I am thinking that it is only looking at one date
possibly when I enter the link parent/child information (?).

Again, any information that you could provide would be very much
appreciated. I can change my query so I am only looking at one year at a
time, but I would rather run it once, and get several years worth of data.

Thanks, Jm

 

[Jasonm]

That did it! Thanks. It took me a bit of fiddling to get the field created
in the chart query...

Thanks a ton!

Jm

I would think you would want to link on the same value as the group. Try
add
a year field/calculation to your Row Source for the chart and link it to a
year value in the Year Footer.
--
Duane Hookom
Microsoft Access MVP

I appreciate the effort and sympathize about not having enough
information...

The chart data source is as follows:
SELECT (Format([CTDate],"MMM 'YY")),Avg([ClearwellLogRemoval]) AS
[Clearwell
Log Removal],Avg([SedBasinLogRemoval]) AS [Sed Basin Log
Removal],Avg([TOTALreduction]) AS [TOTAL Log Removal] FROM
[qryDisinfectionProfile_2] GROUP BY (Year([CTDate])*12 +
Month([CTDate])-1),(Format([CTDate],"MMM 'YY"));

The report has two groups, both on the date field CTDate. On is grouped
on
Year and the onther on Month. The data is displayed in the Months header
section and summarized in the Year footer where the chart is placed.

I have tried entering in the CTDate manually in the link parent/link
child,
but then NO data displays. I am thinking that it is only looking at one
date
possibly when I enter the link parent/child information (?).

Again, any information that you could provide would be very much
appreciated. I can change my query so I am only looking at one year at a
time, but I would rather run it once, and get several years worth of
data.

Thanks, Jm

You don't have to use the "..." button to create the link. Simply type
in
the
field names.

It's a bit difficult to trouble-shoot report/chart issues without being
privy to your Record Source and Row Source properties as well as the
section
of the report containing the chart.
--
Duane Hookom
Microsoft Access MVP


:

I have tried that, but am getting a bizarre error stating that I
"connot
build a lik between unbound forms!"

This doesn't make any sense to me because the fom is bound to a query,
and
when I ran the chart wizard it gave me the option to link the
fields...
However when I run the report no data is displayed in the chart until
I
remove the links between parent and child...

Sorry it took me so long to post back, I ended up working all day, and
didn't get to check back in... I will keep trying a few things, to see
what
I can do, but any suggestions would be greatly appreciated!

Thanks! Jm
The graph control is somewhat similar to a subreport. You can set
the
Link
Master/Child properties to synch the years.
--
Duane Hookom
Microsoft Access MVP


:

Good evening all! I have been working on adding a line graph on a
report
that spans several years and data is grouped by year and month.
Data
is
displayed as Average Monthly Values, and then summarized in the
Year
group
footer (if that makes any sense!). I wanted the chart to be in the
Year
Group footer and ONLY display the data from the year that that
footer
represnets.

The graph displays ALL of the data from the full range of the
query.
Can
this be done? I guess that I was assuming that since I placed the
chart
in
this footer that it would be like the controls in the footer and
only
summarize the data from the year...

Any assistance would be greatly appreciated.

Thanks, Jm