DataBase - Bar Chart based on report calculations...

Asked By Ann

27-Jan-10 11:05 AM

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 is 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 cannot 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.

27-Jan-10 02:16 PM

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
santus@penn.com

28-Jan-10 02:24 PM

I am really rather lost by what you are explaining.  I do not understand how to
get a textbox to adjust it is 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?

28-Jan-10 02:38 PM

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)

II

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

I am really rather lost by what you are explaining.  I do not understand how
to
get a textbox to adjust it is 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?

28-Jan-10 02:48 PM

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

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
santus@penn.com