Bar Chart based on report calculations...

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

I am using Access 2002. I have a query with one field called
txtUsedKnowledge that has four possible responses. They are Never, Small
Extent, Moderate Extent and Great Extent.

I did many calculations with these including counting them individually and
getting the percent for each one based on the over all count. Now I would
like to create a bar chart with the four responses listed that will update
each time it's run.

I created the chart but have to type in the figures for each response
myself. Since the calculations are done on the report and not in the query
is there a way to use the fields from the report. Unfortunately, this is my
first time trying to use the Chart feature in Access. If I can't do this
with the Chart option is there another way to have a bar appear on a report?
Thank you in advance for any help that will be offered.
 
Since there are only four elements, I would not create a bar chart. I would
use a textbox for each element and adjust the width of each textbox to
represent the percent of each. It would look like:
____________
Never [____________] 35%
______
Small Extent [______] 15%
__________
Moderate Extent [__________] 30%
________
Greate Extent [________] 20%

You can do all the calculations (count, percent, width) in a query. Note
that percent would be shown in a separate textbox and its Left property
would also need to be set by the calculated percent. Since all calculations
are done in a query, the display would aotomatically update as the data
changed.

Steve
(e-mail address removed)
 
I'm really rather lost by what you are explaining. I don't understand how to
get a textbox to adjust it's size based on a number. Plus, my calculations
were not done in the query but on the report in unbound textboxes that are in
the report footer. Can you give me more detail?

Steve said:
Since there are only four elements, I would not create a bar chart. I would
use a textbox for each element and adjust the width of each textbox to
represent the percent of each. It would look like:
____________
Never [____________] 35%
______
Small Extent [______] 15%
__________
Moderate Extent [__________] 30%
________
Greate Extent [________] 20%

You can do all the calculations (count, percent, width) in a query. Note
that percent would be shown in a separate textbox and its Left property
would also need to be set by the calculated percent. Since all calculations
are done in a query, the display would aotomatically update as the data
changed.

Steve
(e-mail address removed)



Ann said:
I am using Access 2002. I have a query with one field called
txtUsedKnowledge that has four possible responses. They are Never, Small
Extent, Moderate Extent and Great Extent.

I did many calculations with these including counting them individually
and
getting the percent for each one based on the over all count. Now I would
like to create a bar chart with the four responses listed that will update
each time it's run.

I created the chart but have to type in the figures for each response
myself. Since the calculations are done on the report and not in the
query
is there a way to use the fields from the report. Unfortunately, this is
my
first time trying to use the Chart feature in Access. If I can't do this
with the Chart option is there another way to have a bar appear on a
report?
Thank you in advance for any help that will be offered.


.
 
Ann,

What Steve is proposing is probably a little more coding. Please post the
SQL of the query you are using to create your chart. Seems to me you could
use the same unbound text boxes on the chart BUT it depends on the query
which might need a little modfication or not...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm really rather lost by what you are explaining. I don't understand how
to
get a textbox to adjust it's size based on a number. Plus, my calculations
were not done in the query but on the report in unbound textboxes that are
in
the report footer. Can you give me more detail?

Steve said:
Since there are only four elements, I would not create a bar chart. I
would
use a textbox for each element and adjust the width of each textbox to
represent the percent of each. It would look like:
____________
Never [____________] 35%
______
Small Extent [______] 15%
__________
Moderate Extent [__________] 30%
________
Greate Extent [________] 20%

You can do all the calculations (count, percent, width) in a query. Note
that percent would be shown in a separate textbox and its Left property
would also need to be set by the calculated percent. Since all
calculations
are done in a query, the display would aotomatically update as the data
changed.

Steve
(e-mail address removed)



Ann said:
I am using Access 2002. I have a query with one field called
txtUsedKnowledge that has four possible responses. They are Never,
Small
Extent, Moderate Extent and Great Extent.

I did many calculations with these including counting them individually
and
getting the percent for each one based on the over all count. Now I
would
like to create a bar chart with the four responses listed that will
update
each time it's run.

I created the chart but have to type in the figures for each response
myself. Since the calculations are done on the report and not in the
query
is there a way to use the fields from the report. Unfortunately, this
is
my
first time trying to use the Chart feature in Access. If I can't do
this
with the Chart option is there another way to have a bar appear on a
report?
Thank you in advance for any help that will be offered.


.
 
You need to make the recordsource of your report a query. You then can do
your calculations in the query.

<<how to get a textbox to adjust it's size based on a number>>
You need to determine what width you want for 100%. Say 4 inches. That's 4 x
1440 = 5760 twips. Then say in your query you calculate Never percent to be
35%. You set the width of the textbox with:
Me!Never.Width = .35 * 5760

Put that code in the Open event of your report.

Steve
(e-mail address removed)




Ann said:
I'm really rather lost by what you are explaining. I don't understand how
to
get a textbox to adjust it's size based on a number. Plus, my
calculations
were not done in the query but on the report in unbound textboxes that are
in
the report footer. Can you give me more detail?

Steve said:
Since there are only four elements, I would not create a bar chart. I
would
use a textbox for each element and adjust the width of each textbox to
represent the percent of each. It would look like:
____________
Never [____________] 35%
______
Small Extent [______] 15%
__________
Moderate Extent [__________] 30%
________
Greate Extent [________] 20%

You can do all the calculations (count, percent, width) in a query. Note
that percent would be shown in a separate textbox and its Left property
would also need to be set by the calculated percent. Since all
calculations
are done in a query, the display would aotomatically update as the data
changed.

Steve
(e-mail address removed)



Ann said:
I am using Access 2002. I have a query with one field called
txtUsedKnowledge that has four possible responses. They are Never,
Small
Extent, Moderate Extent and Great Extent.

I did many calculations with these including counting them individually
and
getting the percent for each one based on the over all count. Now I
would
like to create a bar chart with the four responses listed that will
update
each time it's run.

I created the chart but have to type in the figures for each response
myself. Since the calculations are done on the report and not in the
query
is there a way to use the fields from the report. Unfortunately, this
is
my
first time trying to use the Chart feature in Access. If I can't do
this
with the Chart option is there another way to have a bar appear on a
report?
Thank you in advance for any help that will be offered.


.
 
Back
Top