PIE Chart and live data on Form
Students’ Exam Scores of five different Subjects are recorded in a Table: tbl_Students. A sample Table image is given below:
The name of the student, from the current record on the Form, is used as criteria in a Query, to filter the exam records of that student, as source data for the PIE Chart on the same Form. Sample Form image with the PIE Chart is given below:
When any one of the exam record, out of five subjects of a particular student become current on the Form the PIE Chart should display the marks of all five subjects and percentage on Maximum Total Marks, on the same Form. This is the tricky part; we cannot design a Graph Chart on the Form, while the Form is in normal view to filter the data in the query as source for the same Chart.
We need three simple queries to filter and prepare the data for the Chart. Besides that we need a temporary table to store the Total of all Obtainable Maximum Marks (to calculate the percentage of total Obtained marks) on each subject for the chart (100 x 5 = 500). The image of the temporary table is given below:
We will divide the task in three parts, so that the procedure is easy to understand:
- Part-A: Create tbl_Students and tmp_MaxMarks.
- Part-B: Design Form frm_Students for tbl_Students and create three simple Queries to prepare data for the Chart.
- Part-C: Create a PIE Chart on frm_Students Form.
- Create a Table with the Structure, as shown in the first image on the top and name the table as tbl_Students. You can ignore the two empty fields on the Table. As you can see the table contains three students’ exam scores of five subjects, out of hundred each.
- Key-in those sample data into tbl_Students Table.
- Create a small table with two fields, with the following structure and save it with the name tbl_MaxMarks.
- Create a single record with the sample data, shown on the image above.
- Design a Form in Column format, using tbl_Students, leaving enough space at the right side to create the PIE Chart. You can use the Form Wizard to create the Form quickly and save the Form with the name frm_Students.
We will create three Queries, before we proceed with some more work on the Form.
- Create the following Queries by copying, pasting the SQL String into new Query’s SQL Editing Window and saving it with the name as indicated:
SELECT [Student] & " " & [Subject] AS [Desc], tbl_Students.Marks FROM tbl_Students WHERE (((tbl_Students.Student)=[forms]![frm_Students]![student]));
SELECT tmp_MaxMarks.Desc, [MaxMarks]-DSum("Marks","StudentsQ") AS Max_Marks FROM tmp_MaxMarks;
SELECT StudentsQ.* FROM StudentsQ UNION ALL SELECT MaxMarksQ.* FROM MaxMarksQ;
- Open frm_Students in Design View.
- Create two Text Boxes below the existing fields.
- Click on the first Text box and press F4 to display the Property Sheet.
- Change the Name Property value to Obtained.
- Type the following expression into the Control Source Property:
- Change the Caption Property Value, of the Child Label of the Textbox, to Total Obtained:.
- Click on the second Textbox to select it.
- Write the following expression into the control Source Property:
- Change the Format Property Value to Percentage.
- Change Caption Property value of the Child Label of the Textbox to % on Total:.
- Save the Form and open it in Normal View.
Let us run a review of what we did so far.
We have created the main table tbl_Students and designed the Form frm_Students, using tbl_Students.
While the frm_Students is active, the StudentsQ filters the data using the current student’s name (say John) on the Form as criteria.
Second Query (MaxMarksQ) Sum ups the total obtained marks from StudentsQ. Subtracts it from Maximum Marks of all five subjects (500) from tmp_MaxMarks. The difference of these two values,if any, is used for calculating the percentage of marks the student lost. In other words if this figure on the PIE chart shows as 10% then the student could only obtain 90% aggregate marks out of 500.
Third Query: UnionPieChart combines the Data of StudentsQ and MaxMarksQ queries and used as Data Source for the PIE Chart.
Our PIE Chart should be created on the frm_Students, but the Form should be kept open in normal view to filter the data in StudentsQ for the chart. We can create the Chart only when the form is in design view. I hope you can see now why we need a little trick to pull this off.
Now, the frm_Students Form is in open state and the exam data of John (the first student on the Form) is filtered and available in StudentsQ. We should go through the following steps to create the PIE Chart on the frm_Students.
- While keeping the frm_Students in Form View, open a new Form in Design View.
- Enable Use Control Wizard Button on the Toolbar above and select Insert Chart Tool from the Controls group.
- Draw a Chart in rectangular shape, large enough to show the formatting (like Value Labels, Title etc.) we are going to do, like the sample image given above.
- Select Queries from the View group from the Wizard control and select UnionPieChart from the displayed query list.
- Click Next to proceed to the next screen and click on >> button to select all the displayed fields for the chart.
- Click on the PIE Chart Icon to select it and click Next button twice then type the following as title on the Title control:
Max. Marks Each=100, 500 Total
- Select No, don’t display a Legend Radio-button then Click Finish.
- Double-Click on the PIE Chart to change it into Edit Mode.
- Right-Click on an empty area of the Chart to display the context menu.
- Select Chart Options from the displayed Menu.
- Select the Data labels Tab and put Check Marks in Category Name, Values and Percentage.
- Click OK to close the control.
We will reduce the size of the PIE slice labels to a smaller font size.
- Click on one of the PIE slice Labels. this action will select all the Labels together.
- Change the Font size to something like: 12 points, using the Font/Size Tool above.
Completed design of PIE Chart is given below.
- Click outside the Chart Object on the Form, to exit from Chart-Edit Mode, but the chart will be still in selected state.
- Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clip-Board.
- You may save the Form with a name and close it, if you would like to keep it safe. But, we don’t need it any more.
- Now, Change the frm_Students into Design View.
- Click somewhere on the Detail Section of the Form to make Detail Section current.
- Right-click on the Detail Section and select Paste from the displayed menu.
- Drag the PIE Chart and place it to the right side of data fields.
- Click on left top corner of the Form and display the Property Sheet (F4) of the Form.
- Select the Event Tab of the Property Sheet and click on the On Current property.
- Select Event Procedure from the drop-down list and click on the build (. . .) button to open the VBA editing window.
- Write the following statement in the middle of the Form_Current() Event Procedure:
When completed the procedure will look like the following:
Private Sub Form_Current() Me.Refresh End Sub
When you move the records on the Form, from one to the other,the Form_Current() event procedure will update the recordset with the change on the StudentsQ as well.
- Close the VBA editing window.
- Save the frm_Students Form and open it in Normal View.
The first five records on the Form belongs to the student John. If you move one record at a time you will not find any difference on the Chart up to the fifth record, because values of all the five records are shown on the PIE Chart. The sixth to tenth records belongs to the second student and 11th record onwards belongs to the third student. You may type 6 or 11 on the Record Navigation Control directly to quickly display other students marks on PIE Chart.