All I want for Christmas is a Line Chart!

I need to create a line chart. I have a query that displays about 100 rows
of data with the following fields:

Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

In a report, I grouped the staff_section into a header in order to sum
values for Q1_10, Q2_10, etc. I want to plot these summed values on a line
chart with each quarter on the x axis and values on the y axis. If the chart
contains only one staff section, there will be only one line. If it contains
multiple lines, I can make a different color for each staff section. For the
report I am currently working with, the query has criteria to show only one
staff section.

Any help is appreciated. I am fairly good with Access, but not proficient
in SQL or code. Thanks for your time!

Glen

Did you sit on Santa's lap (run the chart wizard)? If not, I would start by
creating a query that displays the exact values you want on your chart. Then
use the chart wizard to add the chart control where ever you want it on the
report. Typically the wizard will mangle your query so you have to go back in
and change the Row Source.

--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

I need to create a line chart. I have a query that displays about 100 rows
of data with the following fields:

Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

In a report, I grouped the staff_section into a header in order to sum
values for Q1_10, Q2_10, etc. I want to plot these summed values on a line
chart with each quarter on the x axis and values on the y axis. If the chart
contains only one staff section, there will be only one line. If it contains
multiple lines, I can make a different color for each staff section. For the
report I am currently working with, the query has criteria to show only one
staff section.

Any help is appreciated. I am fairly good with Access, but not proficient
in SQL or code. Thanks for your time!

Glen

We are very close Duane! I almost have what I need, but I ask you for just a
little more help

I was able to create a query that shows data as below:


Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

Operations 144 173 192 205 217 252
325 346


When I use the chart wizard, I am only able to select up to 6 fields of
data. As you can see from above, I have more than 6. Please help!

Glen



"Duane Hookom" wrote:

Did you sit on Santa's lap (run the chart wizard)? If not, I would start by
creating a query that displays the exact values you want on your chart. Then
use the chart wizard to add the chart control where ever you want it on the
report. Typically the wizard will mangle your query so you have to go back in
and change the Row Source.

--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

I need to create a line chart. I have a query that displays about 100 rows
of data with the following fields:

Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

In a report, I grouped the staff_section into a header in order to sum
values for Q1_10, Q2_10, etc. I want to plot these summed values on a line
chart with each quarter on the x axis and values on the y axis. If the chart
contains only one staff section, there will be only one line. If it contains
multiple lines, I can make a different color for each staff section. For the
report I am currently working with, the query has criteria to show only one
staff section.

Any help is appreciated. I am fairly good with Access, but not proficient
in SQL or code. Thanks for your time!

Glen

That's what I mean when I suggest the Wizard mucks things up. You just select
6 and then go back in after the wizard is done and set the Row Source
property to what you want.

Keep in mind your data is in an un-normalized format (across rather than
down) so you may need to set a property in the Chart Design view to across vs
down or something like that.

Ho, Ho, Ho!
--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

We are very close Duane! I almost have what I need, but I ask you for just a
little more help

I was able to create a query that shows data as below:


Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

Operations 144 173 192 205 217 252
325 346


When I use the chart wizard, I am only able to select up to 6 fields of
data. As you can see from above, I have more than 6. Please help!

Glen



"Duane Hookom" wrote:

Did you sit on Santa's lap (run the chart wizard)? If not, I would start by
creating a query that displays the exact values you want on your chart. Then
use the chart wizard to add the chart control where ever you want it on the
report. Typically the wizard will mangle your query so you have to go back in
and change the Row Source.

--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

I need to create a line chart. I have a query that displays about 100 rows
of data with the following fields:

Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

In a report, I grouped the staff_section into a header in order to sum
values for Q1_10, Q2_10, etc. I want to plot these summed values on a line
chart with each quarter on the x axis and values on the y axis. If the chart
contains only one staff section, there will be only one line. If it contains
multiple lines, I can make a different color for each staff section. For the
report I am currently working with, the query has criteria to show only one
staff section.

Any help is appreciated. I am fairly good with Access, but not proficient
in SQL or code. Thanks for your time!

Glen

Ok...

The good news: I have a line chart in the format I want with the correct
data.

The bad news: When I add another row in the Row Source properties box in
report design view and view the report in report view, the chart becomes a
default display chart of some sort that has nothing to do with my data. It
seems that once I exceed 6 data fields in the row source, I cannot get what I
want. The chart wizard also mentions this. What did I do to deserve this
Duane? I am so close! Can you carry me home???

Glen



"Duane Hookom" wrote:

That's what I mean when I suggest the Wizard mucks things up. You just select
6 and then go back in after the wizard is done and set the Row Source
property to what you want.

Keep in mind your data is in an un-normalized format (across rather than
down) so you may need to set a property in the Chart Design view to across vs
down or something like that.

Ho, Ho, Ho!
--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

We are very close Duane! I almost have what I need, but I ask you for just a
little more help

I was able to create a query that shows data as below:


Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

Operations 144 173 192 205 217 252
325 346


When I use the chart wizard, I am only able to select up to 6 fields of
data. As you can see from above, I have more than 6. Please help!

Glen



"Duane Hookom" wrote:

Did you sit on Santa's lap (run the chart wizard)? If not, I would start by
creating a query that displays the exact values you want on your chart. Then
use the chart wizard to add the chart control where ever you want it on the
report. Typically the wizard will mangle your query so you have to go back in
and change the Row Source.

--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

I need to create a line chart. I have a query that displays about 100 rows
of data with the following fields:

Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

In a report, I grouped the staff_section into a header in order to sum
values for Q1_10, Q2_10, etc. I want to plot these summed values on a line
chart with each quarter on the x axis and values on the y axis. If the chart
contains only one staff section, there will be only one line. If it contains
multiple lines, I can make a different color for each staff section. For the
report I am currently working with, the query has criteria to show only one
staff section.

Any help is appreciated. I am fairly good with Access, but not proficient
in SQL or code. Thanks for your time!

Glen

Woohoo!!!!

I figured it out!!!! I am so happy now!! I must be a real dork because I
get so excited when this stuff actually works!!! Thanks so much Duane!!!
Merry Christmas!!!

Glen

"Duane Hookom" wrote:

That's what I mean when I suggest the Wizard mucks things up. You just select
6 and then go back in after the wizard is done and set the Row Source
property to what you want.

Keep in mind your data is in an un-normalized format (across rather than
down) so you may need to set a property in the Chart Design view to across vs
down or something like that.

Ho, Ho, Ho!
--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

We are very close Duane! I almost have what I need, but I ask you for just a
little more help

I was able to create a query that shows data as below:


Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

Operations 144 173 192 205 217 252
325 346


When I use the chart wizard, I am only able to select up to 6 fields of
data. As you can see from above, I have more than 6. Please help!

Glen



"Duane Hookom" wrote:

Did you sit on Santa's lap (run the chart wizard)? If not, I would start by
creating a query that displays the exact values you want on your chart. Then
use the chart wizard to add the chart control where ever you want it on the
report. Typically the wizard will mangle your query so you have to go back in
and change the Row Source.

--
Duane Hookom
Microsoft Access MVP


"*Glen*" wrote:

I need to create a line chart. I have a query that displays about 100 rows
of data with the following fields:

Staff_section Q1_10 Q2_10 Q3_10 Q4_10 Q1_11 Q2_11 Q3_11 Q4_11

In a report, I grouped the staff_section into a header in order to sum
values for Q1_10, Q2_10, etc. I want to plot these summed values on a line
chart with each quarter on the x axis and values on the y axis. If the chart
contains only one staff section, there will be only one line. If it contains
multiple lines, I can make a different color for each staff section. For the
report I am currently working with, the query has criteria to show only one
staff section.

Any help is appreciated. I am fairly good with Access, but not proficient
in SQL or code. Thanks for your time!

Glen

*Glen* wrote:

Woohoo!!!!

I figured it out!!!! I am so happy now!! I must be a real dork because I
get so excited when this stuff actually works!!! Thanks so much Duane!!!
Merry Christmas!!!


I know what you mean, but that's more typical of a geek, not
a dork ;-)

Merry Christmas to you too.

--
Marsh
MVP [MS Access]

You are experiencing a "progasm" ;-)
--
Duane Hookom
Microsoft Access MVP


"Marshall Barton" wrote:

*Glen* wrote:

Woohoo!!!!

I figured it out!!!! I am so happy now!! I must be a real dork because I
get so excited when this stuff actually works!!! Thanks so much Duane!!!
Merry Christmas!!!


I know what you mean, but that's more typical of a geek, not
a dork ;-)

Merry Christmas to you too.

--
Marsh
MVP [MS Access]
.