Percentage graph

[BruceM]

I have a query (Access 2003) that calculates percentages. A continuous form
bound to the query produces records according to selections from
multi-select list boxes in the header. For instance, let's say there is a
list box for Month and another for Department. If I don't select a month
but I select Shipping for the department, I could end up with the following
records:

January.......Shipping..........85%
February.....Shipping..........95%
March.........Shipping..........91%

If I select Shipping and Machining:

January........Shipping.........85%
January........Machining.......97%
February......Shipping.........95%
etc.

These are the correct results. The trouble is that I would like to turn
them into a bar graph. Each row in the sample data is a record in a
continuous form. It would be just about perfect if I could have a label or
text box that grows according to the percentage. I can turn the percentage
into a number and multiply it by a constant producing a number of twips such
that 100% is a 5" bar, 50% is a 2.5" bar, etc., but of course the width of
the text box is a property of the form. If I set the width programatically
it applies the calculated measurement from the first subform record to the
text box in all of the subform records. I understand that this is the
expected behavior, but I wonder if there is a way to implement the desired
result.

The graph tool could sort of work OK if I could figure out how to make it
respond to the list box selections; that is, its source data would need to
mirror the source data in the customized recordset produced by the list box
selections. Since the Detail section is a continuous form, I suppose the
graph would need to go into the footer (assuming it will work at all). If
it matters, the list boxes are setting the recordsource SQL (in essence, by
applying WHERE provisions based on the list box selections) rather than
filtering the records.

Anyhow, the variable-width text box (or label, or whatever) would be my
first choice, I think, unless the graph is more flexible than I imagine.

Also, I understand that reports will give me sorting and grouping options
that are problematic at best on forms. If one of the options is to have a
report pop up in a reduced-size window after the list box selections are
made, or something like that to show the data in graph format, that would be
another option, but it seems as if it is starting to get pretty clunky if I
go that route. However, the idea is that the whole thing needs to be
interactive to the largest extent possible. If the user selects no filter,
then decides to look at records just for Shipping, it needs to be possible
to do so readily.

I would welcome any thoughts beyond my speculations, if I am overlooking
something that could be useful.

 

[Peter Hibbs]

Bruce

A Flex Grid Control will probably do what you want. Have a look at -

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

for some examples.

Peter Hibbs.

 

[BruceM]

Thanks for directing my attention to the utility, which is very impressive.
Unfortunately, I don't think it will be able to do what I need in this
situation (text boxes of different widths in a continuous form, with the
width dependent on a calculated value in each record), but it has a number
of features (such as the varying row heights) that could prove useful in
other projects.

 

[Peter Hibbs]

Bruce

I disagree, I think a Flex Grid would do exactly what you want. Of
course, you know your database better than I do but if I understand
your problem, you have a number of values which you have calculated as
percentages and you need some way of displaying those percentage
values as a bar graph on a form (unless I have misread your question).

I have made up a small database file with a Flex Grid control which
displays a bar graph of Northwind orders per month as a percentage of
the yearly total. It works fine and I think you could use the basic
method to do what you want without using continuous forms, expanding
text boxes, or whatever. If you would like a copy just email me at -
peter dot hibbs at btinternet dot com
and I would be happy to send you a copy.

Peter Hibbs.

 

[BruceM]

Thanks for replying. Perhaps I have misunderstood how the Flex Grid works.
Without getting into a lot of details, I have combined two crosstab queries
into a totals query along the lines of the method described in KB 304458.
The percentage is a calculation within the totals query. The percentage is,
in effect, a numeric value within a record rather than a calculation
involving several records. If three records show 50%, 75%, and 100%, I
would like to see (where each bracket character represents 5%):

]]]]]]]]]]
]]]]]]]]]]]]]]]
]]]]]]]]]]]]]]]]]]]]

Of course, I want those to be bars, but this is the best way I could think
to represent them with text.

I appreciate the offer to send the sample database. If nothing else it
sounds like the Flex Grid is something I will be able to use in various
situation. Maybe it will adapt to the current situation. I will contact
you via e-mail.