September 2013 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Students' Exam Scores in five different Subjects are recorded in 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. The Sample Form- image with the PIE Chart is given below:

When any one of the exam records, out of five subjects of a particular student becomes current on the Form the PIE Chart should display the marks of all five subjects and the percentage of 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 the source for the same Chart.

Data Source Queries.

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:

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 frm_Students Form.

Part-A:

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 for five subjects, out of hundred each.
Key in those sample data into tbl_Students Table.
Create a small table with two fields, with the structure shown in the above image, 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 Column format, using tbl_Students, leaving enough space on 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 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 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 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 up the total obtained marks from StudentsQ. Subtracts it from the Maximum Marks of all five subjects (500) from tmp_MaxMarks. The difference between 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 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 is used as Data Source for the PIE Chart.

Part-C:

Our PIE Chart should be created on the frm_Students, but the Form should be kept open in a 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 the open state and the exam result 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 a 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 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.
Click outside the Chart Object in the Form, to exit from Chart-Edit Mode, but the chart will be still in the selected state.
Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clipboard.
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 anymore.
Now, Change the frm_Students into Design View.
Click somewhere 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 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 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 the five records are shown on the PIE Chart. The sixth to tenth records belongs 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 PIE Chart.