Graphically Display Percentages on Forms and Reports
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Graphically Display Percentages on Forms and Reports
by Sean Kavanagh
Application: Microsoft Access 97/2000/2002
Download from the Element K Journals website.
There are many instances when showing percentage data in a graphical format is useful to an application user. For example, if you think of what happens when you install new software, you're usually presented with a dialog box that shows the percentage completed for the task. This allows you to judge the remaining time needed to finish the job, but such approaches are applicable whenever you want to compare progress toward a defined goal.
In the case of Access, you can graphically display percentage data by using a technique submitted by William Stanley of South Burlington, Vt. As you can see in Figure A, graphically presenting percentages allows you to easily compare items and provide a quick snapshot of progress. In this article, we'll look at how to implement such graphical progress meters. We'll use a form in our example, but you could just as well apply the technique with reports.
Figure A: Being able to visually see the percentage complete provides a quick snapshot of progress.
Simulating a progress meter
The technique we'll use is deceptively simple. Rather than use something like Excel or Microsoft Graph, we'll format an unbound textbox control to display the percentage complete. The control will be sized to hold 100 lowercase L characters (one for each segment of the completion bar). Then, we'll set the control's Control Source to a calculation that determines the percentage complete and fills the textbox with the appropriate number of characters.
To illustrate the technique, we'll create a stripped down version of the form shown in Figure A. The depicted form is used to track progress toward meeting commitments that individuals made to participate in a volunteer program. We'll start by setting up and populating three tables: one that holds the volunteer data, one that contains the dates they've been scheduled to work, and one that stores the dates that they actually have worked. After setting up the tables, we'll create two Totals queries that determine the number of days scheduled and worked. The results of these queries will form the basis for each person's time commitment goal and percentage completed. Finally, we'll create the status form that graphically displays the percentage data.
Set up the tables
To begin, create a new database and set up three tables using the specifications shown in Table A. Then, populate the tables with sample data. We'll use the sample data shown in Figure B, but you can use your own. Note that you can also download the completed database from the FTP address listed at the beginning of this article. Once you've set up your data, close the tables.
Table A: Table structures
Figure B: We'll graphically show how far the day credits stored in tblWorkDays go toward satisfying the volunteer commitments that have been made.
Create the Totals queries
Now we'll create the queries that determine the number of days that volunteers have committed to and what percentage of each individual's goal has been met. Note that there are many approaches you could take to deduce this data. We're simply going to count the number of days in tblSchedules and sum the credits stored in tblWorkDays, without tying completed work to specific dates.
To create the first query, which will count committed days, choose Insert | Query from the menu bar. Then, double-click on Design View. When the Show Table dialog box appears, select tblSchedules, click Add and then click Close.
At this point, drag VolunteerID and SchedDate from the tblSchedules field box to the query design grid. Next, choose View | Totals from the menu bar. In the SchedDate column, select Count from the Total dropdown list. Run the query, and you'll see that it returns the appropriate number of days each volunteer is scheduled for, as shown in Figure C. Finally, save the query as qryScheduled and close it.
Figure C: Our Totals query reveals that two people have volunteered to work seven days, and one has committed to 12 days.
The query you just created figures out the commitment each individual made. Now you need to determine how much work they've actually completed. To create the second query, open a new query in Design view and add tblVolunteers, tblWorkDays and qryScheduled to it.
Access should automatically join the tables and query as needed in order to get the results you need. However, if it doesn't, you can set up the joins manually. To do so, drag and drop the VolunteerID field from the tblVolunteers box to the corresponding field in the tblWorkDays box. Then, repeat the process to join tblVolunteers and qryScheduled.
|Tip: For more information about joining tables and queries, see the article "Relational database fundamentals: Table joins and referential integrity" in this month's issue.|
To finish creating the query, select View | Totals from the menu bar. Then, using Figure D for the details, set up the appropriate field selections and Total settings. When you're finished, run the query. As you can see in Figure E, you can compare the number of days worked to the number of days originally scheduled. At this point, save the query as qryWorkVsSched and close it.
Figure D: Join the tables and query with the VolunteerID field, then use these specs to complete the second Totals query.
Figure E: Incorporating the original Totals query into the new one lets you compare days worked to days scheduled.
Build the basic form
Now that you have the raw data needed to determine the percentage of work commitments that have been fulfilled, you can set up the form that graphically displays those percentages. First, select qryWorkVsSched in the Database window. Then, choose Insert | Form from the menu bar. When the New Form dialog box appears, double-click on Design View.
From the menu bar, select View | Form Header/Footer. Then, using Figure F as a guide, resize the form sections and window appropriately. Next, add the Name, SumOfDayCredit and CountOfSchedDates fields to the form, along with the appropriate labels in the Form Header section.
Figure F: Resize the form sections based on this sample, then add the appropriate controls.
Set the Name property for the text box displaying the SumOfDayCredit data to txtWorked. Set the CountOfSchedDates field's text box name to txtSchedule. Then, set the form's Default View property to Continuous Forms and the Form Footer section's Visible property to No. Finally, save the form as frmWorkVsSched.
Add the percentage information
To set up the controls that display the visual representation of the percentage and the actual percentage number, add two unbound text boxes beneath the Percentage Complete and % labels, respectively. Then, use the settings shown in Table B to properly format the Percentage Complete text box. Note that the control Width specification is based upon the font attributes we used, to ensure that the text box properly reflects the percentage gauge it displays. If you use different font settings in your own applications, you'll need to adjust the size accordingly.
Table B: Property settings for the percentage meter
|Font Name||MS Sans Serif|
Now we're ready to add the percentage calculations. Set the Control Source property of the textbox you're working with to:
The secret to the technique is the
String() function, which is used to repeat a character in order to build a string of a specified length. We use an
IIf() function to conditionally display either a full percentage meter (100 repeated l characters) or a number of characters equal to the percentage of days worked, as defined by
To display the numeric percentage, select the remaining unbound textbox and set its Control Source property to:
When you're finished, save the form and switch to Form view. Your results should resemble the example shown in Figure G. As you can see, it's easy to tell who is well on their way toward meeting their goals, and who still has work to do.
Figure G: Graphically showing the percentages lets you see at a glance that Mary LaFave is the closest to completing her goal.
Progress evaluation at a glance
When goals can be represented by data stored in a database, you can view the progress using forms and reports—and a graphical representation of the progress has a lot more impact than a number buried amongst other data. We've shown you one application that lends itself to the use of a percentage meter, but you aren't limited to displaying percentages relevant to progress. You can use the technique we presented to add some visual flair to forms and reports any time you have percentage data, so you should easily be able to apply the approach to your own business applications.
Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.