Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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. 

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 in three parts, so that the procedure is easy to understand:

  1. Part-A: Create tbl_Students and tmp_MaxMarks.
  2. Part-B: Design Form frm_Students for tbl_Students and create three simple Queries to prepare data for the Chart.
  3. Part-C: Create a PIE Chart on frm_Students Form.


  1. 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.
  2. Key-in those sample data into tbl_Students Table.
  3. Create a small table with two fields, with the structure shown on the above image and save it with the name tbl_MaxMarks.
  4. Create a single record with the sample data, shown on the image above.


  1. 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.

  2. 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:

    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;
  3. Open frm_Students in Design View.
  4. Create two Text Boxes below the existing fields.
  5. Click on the first Text box and press F4 to display the Property Sheet.
  6. Change the Name Property value to Obtained.
  7. Type the following expression into the Control Source Property:
  8. Change the Caption Property Value, of the Child Label of the Textbox, to Total Obtained:.
  9. Click on the second Textbox to select it.
  10. Write the following expression into the control Source Property:
  11. Change the Format Property Value to Percentage.
  12. Change Caption Property value of the Child Label of the Textbox to % on Total:.
  13. 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.

  1. While keeping the frm_Students in Form View, open a new Form in Design View.
  2. Enable Use Control Wizard Button on the Toolbar above and select Insert Chart Tool from the Controls group.
  3. 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.
  4. Select Queries from the View group from the Wizard control and select UnionPieChart from the displayed query list.
  5. Click Next to proceed to the next screen and click on >> button to select all the displayed fields for the chart.
  6. 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

  7. Select No, don't display a Legend Radio-button then Click Finish.
  8. Double-Click on the PIE Chart to change it into Edit Mode.
  9. Right-Click on an empty area of the Chart to display the context menu.
  10. Select Chart Options from the displayed Menu.
  11. Select the Data labels Tab and put Check Marks in Category Name, Values and Percentage.
  12. Click OK to close the control.

    We will reduce the size of the PIE slice labels to a smaller font size.

  13. Click on one of the PIE slice Labels. this action will select all the Labels together.
  14. Change the Font size to something like: 12 points, using the Font/Size Tool above. 

    Completed design of PIE Chart is given below.

  15. Click outside the Chart Object on the Form, to exit from Chart-Edit Mode, but the chart will be still in selected state.
  16. Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clip-Board.
  17. 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.
  18. Now, Change the frm_Students into Design View.
  19. Click somewhere on the Detail Section of the Form to make Detail Section current.
  20. Right-click on the Detail Section and select Paste from the displayed menu.
  21. Drag the PIE Chart and place it to the right side of data fields.
  22. Click on left top corner of the Form and display the Property Sheet (F4) of the Form.
  23. Select the Event Tab of the Property Sheet and click on the On Current property.
  24. Select Event Procedure from the drop-down list and click on the build (. . .) button to open the VBA editing window.
  25. 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()
    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.

  26. Close the VBA editing window.
  27. Save the frm_Students Form and open it in Normal View.

The first five records on the Form belongs 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 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.

  1. MS-Access and Graph Charts
  2. MS-Access and Graph Charts-2
  3. Working With Chart Object in VBA
  4. Column Chart and VBA
  5. Pie Chart Object and VBA
  6. Missing Lines in Line-Chart
  7. Pie Chart and Live Data on Form
  8. Scaling Chart Object
  9. Cross-Tab Union Queries for Chart

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Menus and Toolbars Objects Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Combo Boxes Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Key Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances Item Macros Menus RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference TreeView Control Utility Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers ImageList Control Import Labels List ListView Control Logo Macro Mail Merge Main Form Memo Monitoring Nodes Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code