Project: Using Visual Basic for Applications (VBA), provide an Access
2000 report which will draw a color coded line graph based on a
distribution of percentages.
Background: A number of facilities are reporting their performance by
providing simple numerator/denominator information on a variety of
indicators (or target areas).
- There are 7 variables to account for: target area, time period,
facility ID, percentage, high percentage limit, low percentage limit and
- The user provides 4 of these variables on an Access form which opens
the report. These variables are target area, time period, high limit and
- Each report/graph should only display one target area and time period
at a time. The graph itself displays the facility IDs across the X-axis
and each facility's percentage (0 to 100) along the Y-axis.
- The high limit is a horizontal line drawn across the graph at the
high percentage given by the user. The low limit is a horizontal line
drawn at the low percentage given. The median is a horizontal line draw
across the graph by the median calculated.
- Each data point on the graph must be color coded. If the percentage
is greater than or equal to the high percentage limit it must colored
red. If the percentage is less than or equal the low percentage limit it
must be colored green. If the percentage falls between these high and
low percentage limits it must be colored black.
What already exists:
- A VBA function which will create the table needed for these graphs.
The functions parameters are target area and time period. This returns
an Access table which contains the facility IDs and their respective
- A VBA function which determines the percentage at the high percentage
limit or low percentage limit. When the user enters the high and low
percentage limits, they are actually entering a percentile value. The
VBA function simply takes this value and a boolean value to indicate
high or low and returns back a real number which is the percentage limit
- A VBA function to calculate the median value in the distribution.