Chart help

A while back, I posted a question about charts. After some help from others
users, I thought I had it figured out, but I don't. Here's what I need: A
chart that shows referrals to a specific program over the course of a year.
I can get the data to show up, but I can't get it to format correctly. For
instance, since there are 12 months in a year, the chart shows up 12 times -
once for each month. The charts each show all 12 months, so the additional,
unneeded 11 charts are just duplicates of the first. Also, I need the graph
to display as Jan, Feb, March, etc. However, if I use the month name rather
than the month number in the underlying query, the chart displays
alphabetically, not numerically. Here's the row source as it is now:

TRANSFORM Sum([CountOfProgram]) AS [SumOfCountOfProgram] SELECT
[ReferralMonth] FROM [tblReferralsTEMP] GROUP BY [ReferralMonth] PIVOT
[ReferralYear];

Any help is appreciated. I'm ready to scream...

What are your chart's Link Master/Child properties? Can you link the chart
control to a month on your report? If you expect a different chart for each
month, you need to have each month in the record source of your report.

Your Row Source can sort by one column yet display another column. We don't
know what type of value is stored in ReferralMonth and if you can derive a
month name from it.
--
Duane Hookom
Microsoft Access MVP


"Tara" wrote:

A while back, I posted a question about charts. After some help from others
users, I thought I had it figured out, but I don't. Here's what I need: A
chart that shows referrals to a specific program over the course of a year.
I can get the data to show up, but I can't get it to format correctly. For
instance, since there are 12 months in a year, the chart shows up 12 times -
once for each month. The charts each show all 12 months, so the additional,
unneeded 11 charts are just duplicates of the first. Also, I need the graph
to display as Jan, Feb, March, etc. However, if I use the month name rather
than the month number in the underlying query, the chart displays
alphabetically, not numerically. Here's the row source as it is now:

TRANSFORM Sum([CountOfProgram]) AS [SumOfCountOfProgram] SELECT
[ReferralMonth] FROM [tblReferralsTEMP] GROUP BY [ReferralMonth] PIVOT
[ReferralYear];

Any help is appreciated. I'm ready to scream...

Thanks for getting back to me Duane. I actually found a work-around for this
issue. I'm sure it's not the proper way to do it though and I'd really like
to understand graphs better, so I'll take whatever additional info you can
offer. The report itself was bound to the same table as the chart and the 2
were linked via the field ReferralMonth. I actually only had the report bound
due to needing to get an additional field (Year) to display. I couldn't get
it to become part of the chart since the chart was limiting me to 3 fields.
Binding the report itself was the cause of the 12 graphs. Makes sense once I
stopped and thought about it. Anyway, in oder to work around this, I went
back to my query and combined 2 fields - Program and Year so that my legend
would display both instead of just the Program name.

Does any of that make sense? I'm not sure why, but graphs have always made
my head spin...it's even worse when I try to explain my confusion to others.

"Duane Hookom" wrote:

What are your chart's Link Master/Child properties? Can you link the chart
control to a month on your report? If you expect a different chart for each
month, you need to have each month in the record source of your report.

Your Row Source can sort by one column yet display another column. We don't
know what type of value is stored in ReferralMonth and if you can derive a
month name from it.
--
Duane Hookom
Microsoft Access MVP


"Tara" wrote:

A while back, I posted a question about charts. After some help from others
users, I thought I had it figured out, but I don't. Here's what I need: A
chart that shows referrals to a specific program over the course of a year.
I can get the data to show up, but I can't get it to format correctly. For
instance, since there are 12 months in a year, the chart shows up 12 times -
once for each month. The charts each show all 12 months, so the additional,
unneeded 11 charts are just duplicates of the first. Also, I need the graph
to display as Jan, Feb, March, etc. However, if I use the month name rather
than the month number in the underlying query, the chart displays
alphabetically, not numerically. Here's the row source as it is now:

TRANSFORM Sum([CountOfProgram]) AS [SumOfCountOfProgram] SELECT
[ReferralMonth] FROM [tblReferralsTEMP] GROUP BY [ReferralMonth] PIVOT
[ReferralYear];

Any help is appreciated. I'm ready to scream...

I mostly confused by what you have and what you want.

You can often think of a chart control in the same way you would a
subreport. The both
- have a record/row source,
- generally display values related to a value on the main report
- have link master/child properties
- don't need to display all fields from their source
- can sort on a field/column that isn't displayed
- values can be formatted in a lot of ways

--
Duane Hookom
Microsoft Access MVP


"Tara" wrote:

Thanks for getting back to me Duane. I actually found a work-around for this
issue. I'm sure it's not the proper way to do it though and I'd really like
to understand graphs better, so I'll take whatever additional info you can
offer. The report itself was bound to the same table as the chart and the 2
were linked via the field ReferralMonth. I actually only had the report bound
due to needing to get an additional field (Year) to display. I couldn't get
it to become part of the chart since the chart was limiting me to 3 fields.
Binding the report itself was the cause of the 12 graphs. Makes sense once I
stopped and thought about it. Anyway, in oder to work around this, I went
back to my query and combined 2 fields - Program and Year so that my legend
would display both instead of just the Program name.

Does any of that make sense? I'm not sure why, but graphs have always made
my head spin...it's even worse when I try to explain my confusion to others.

"Duane Hookom" wrote:

What are your chart's Link Master/Child properties? Can you link the chart
control to a month on your report? If you expect a different chart for each
month, you need to have each month in the record source of your report.

Your Row Source can sort by one column yet display another column. We don't
know what type of value is stored in ReferralMonth and if you can derive a
month name from it.
--
Duane Hookom
Microsoft Access MVP


"Tara" wrote:

A while back, I posted a question about charts. After some help from others
users, I thought I had it figured out, but I don't. Here's what I need: A
chart that shows referrals to a specific program over the course of a year.
I can get the data to show up, but I can't get it to format correctly. For
instance, since there are 12 months in a year, the chart shows up 12 times -
once for each month. The charts each show all 12 months, so the additional,
unneeded 11 charts are just duplicates of the first. Also, I need the graph
to display as Jan, Feb, March, etc. However, if I use the month name rather
than the month number in the underlying query, the chart displays
alphabetically, not numerically. Here's the row source as it is now:

TRANSFORM Sum([CountOfProgram]) AS [SumOfCountOfProgram] SELECT
[ReferralMonth] FROM [tblReferralsTEMP] GROUP BY [ReferralMonth] PIVOT
[ReferralYear];

Any help is appreciated. I'm ready to scream...