PIE Chart and live data on Form ~ LEARN MS-ACCESS TIPS AND TRICKS

Saturday, September 28, 2013

PIE Chart and live data on Form

Introduction.

Students' Exam Scores in five different Subjects are recorded in Table: tbl_Students. A sample Table image is given below:

Right-click to open Large Image in New Tab/Window.

The student’s name from the current record on the form is used as a criterion in a query to filter that student’s exam record. These filtered records are then used as the source data for the Pie Chart displayed on the same form. A sample form with the Pie Chart is shown below:

Right-click to open Large Image in New Tab/Window.

When any one of the exam records from the five subjects of a student becomes the current record on the form, the Pie Chart should display the marks of all five subjects, along with their percentage of the maximum total marks, on the same form.

This is the tricky part: we cannot directly design a Graph Chart on the form in normal view to filter the query data that serves as the source for the same chart.

Data Source Queries.

To prepare the data for the chart, we need three simple queries to filter and organize the students’ exam records. In addition, a temporary table is required to store the total of all obtainable maximum marks for each subject (for example, 100 × 5 = 500). This value will be used to calculate the percentage of marks obtained in the chart. The image of the temporary table is shown below:

Right-click to open Large Image in New Tab/Window.

The Three-Part Approach.

We will divide the task into 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 creates three simple Queries to prepare the data for Charts.
Part-C: Create a PIE Chart on the frm_Students Form.

Part-A:

Create a table with the same structure shown in the first image at the top, and name it tbl_Students. You may ignore the two empty fields in the table design. As illustrated, the table contains exam scores for three students, each evaluated in five subjects, with a maximum of 100 marks per subject.
Key in those sample data into the tbl_Students Table.
Create a small table with two fields, as shown in the image above, and save it with the name tbl_MaxMarks.
Create a single record with the sample data, shown in the image above.

Part-B:

Design a form in Columnar format based on tbl_Students, leaving enough space on the right side of the form to accommodate the PIE Chart. You can use the Form Wizard to create the Form quickly. Once completed, save the form with the name frm_Students.
We will create three Queries before working with the Form.

Create the following Queries by copying and pasting the SQL String into the new Query’s SQL Editing Window and saving it with the name as indicated:

Query-1: StudentsQ

SELECT [Student] & " " & [Subject] AS [Desc], tbl_Students.Marks
FROM tbl_Students
WHERE (((tbl_Students.Student)=[forms]![frm_Students]![student]));

Query-2: MaxMarksQ

SELECT tmp_MaxMarks.Desc, [MaxMarks]-DSum("Marks","StudentsQ") AS Max_Marks
FROM tmp_MaxMarks;

Query-3: UnionPieChart

SELECT StudentsQ.* FROM StudentsQ
UNION ALL SELECT MaxMarksQ.* FROM MaxMarksQ;

Open frm_Students in Design View.
Create two TextBoxes 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:
```
=DSum("Marks","StudentsQ")
```
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:
```
=[Obtained]/DLookUp("MaxMarks","tmp_MaxMarks")
```
Change the Format Property Value to Percentage.
Change the Caption Property value of the Child Label of the Text Box to % of Total:.
Save the Form and open it in Normal View.

Let us review 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 data is filtered using the current student's name on the Form as a criterion.

Second Query (MaxMarksQ):

This query sums up the total marks obtained by each student from StudentsQ and compares it against the overall maximum marks (500) stored in tmp_MaxMarks. By subtracting the student’s obtained total from the maximum, we calculate the marks lost.

On the PIE Chart, this difference is represented as a percentage of the total. For example, if the chart shows 10% lost, it means the student secured 90% of the aggregate marks out of 500.

Third Query: UnionPieChart combines the StudentsQ Data and MaxMarksQ queries and is used as a Data Source for the PIE Chart.

Part-C:

Our PIE Chart needs to be displayed on the frm_Students form. However, there’s a challenge:

The form must remain open in Normal View so that the selected student’s record can filter the data in StudentsQ.
At the same time, a chart object can only be created or embedded on a form when it is in Design View.

This is why we need a small trick to accomplish the task.

Let’s assume the form frm_Students is open, displaying John’s record (the first student). His exam data is already filtered and available through StudentsQ. With this in place, follow these steps to create and embed the PIE Chart into the form:

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 a rectangular shape, large enough to show the formatting (like Value Labels, Title, etc.), like the sample image 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 the >> button to select all the displayed fields for the chart.
Click on the PIE Chart Icon to select it and click the Next button twice, then type the following as a heading 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 smaller font sizes.
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.

The completed design of the PIE Chart is given below.
Right-click to open Large Image in New Tab/Window.
Click outside the Chart Object in the Form to exit from Chart-Edit Mode, but the chart will still be in the selected state.
Right-click on the chart to display the shortcut menu, and click Copy to copy the Chart-Object to the Clipboard.
Save the form with a name and close it if you would like to keep it safe. But we don’t need it anymore.
Now, change the frm_Students into Design View.
Click on the Detail Section of the Form to make the Detail section current.
Right-click on the Detail Section and select Paste from the displayed menu.
Drag the PIE Chart and place it on the right side of the data fields.
Click on the top left 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 the 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:
```
Me.Refresh
```
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 record set 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 belong to 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 the values of all five records are shown on the PIE Chart. The sixth to tenth records belong to the second student, and the 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 the PIE Chart.

LEARN MS-ACCESS TIPS AND TRICKS

Saturday, September 28, 2013