MS-Access and Graph Charts

Introduction

Access Reports are excellent at presenting information in numbers and text. But, Charts go one step further in providing information in the form of pictures and a quick look at them says it all. If we know the simple rule that goes for a Chart, then we can create Charts in Access. The simple rule is, to show the change of an event over a period of time in the form of dots and lines in relation to the numbers that they represent, rather than in numbers and text itself.

You must know how to create MS-Access data into a form suitable for the preparation of Graphs. To get some familiarity, we will look into some examples that use only one set of values and the data format that is needed for the graph.

Events like the daily change of day, temperature, or change in body temperature, of patients in a hospital, to monitor their condition, or the change of the gold price over a period of time, etc., can be plotted in the form of Line/Bar Charts on Graph Paper manually. The Scale of the Chart can be determined and marked based on the maximum available value or more with a fixed interval from 0 at the bottom to the top of the Y-Axis (Vertically at the left side or right side Secondary Y-Axis of the Graph). Each day's value must be marked on the graph paper with a dot in relation to the Scale height on the left side and the dots can be joined by lines. The actual value must be placed near the dot. The date will be written at the bottom of the Graph horizontally (X-axis). The description of the Values plotted on the graph like Gold Price is placed as Legend on the Graph. Instead of Pencil and Paper, we can do the same thing with the help of Microsoft Graph Chart Object.

Preparing for a simple Chart

We plan to monitor the daily Stock Index and the values are recorded in an Access Table as given below.

Desc	Date	Val
Stock Index	01-08-07	1245
Stock Index	02-08-07	1455
Stock Index	03-08-07	1395
Stock Index	04-08-07	1575
Stock Index	05-08-07	1125

 

OR

 

Desc	01-08-07	02-08-07	03-08-07	04-08-07	05-08-07
Stock Index	1245	1455	1395	1575	1125

Click to Enlarge

When the above data is plotted in the form of Charts, the contour of the Charts shows the trend of the stock index at a glance rather than reading the numbers and comparing them.

Before preparing the data for the Chart we must decide what Type of Chart is best suited for the data.

For example: If we are presenting a Chart on the Company's Total Revenue from various sources like Vehicle Sales, Parts Sales & Service Sales and show the share of each value to the Total then Pie-Chart will be the ideal one for it.

But, if all the three values of the Pie-Chart (Vehicle Sales, Parts Sales & Service Sales) need to be plotted separately showing the month-wise performance of each category then Line Chart or Bar Chart will be suitable.

If the source data for the graph changes on a fixed interval, every month, then the data source object like Table or Query must be prepared to keep this aspect in mind so that the data automatically appears on the Chart when the source data changes, without manually modifying the Chart every time.

Charts in Excel and MS-Access.

Preparing Charts in Access found not as easy as in Excel. We will look into the most commonly used Chart Types: Line, Bar & Pie Chart examples in Access. Those who worked with Excel Charts may find it difficult to get adjusted to the data preparation methods of Access for charts. But when you are through with the initial difficulties I don't think you will go back to Excel anymore.

But, unlike Excel we have the advantage of several types of Queries and Macros, to automate the process, to skim out figures from large volumes of data quickly and effectively, for any type of Chart. Once it is prepared and set it up, it works every time without manual intervention.

Note: You may attach the Microsoft Excel Object Library to the Access Database library Files List so that we can make use of all the Chart Types and formatting capabilities of Excel can be made available in Access also.

Line and Bar Charts

The Sample Source Data presented in the first format above must be changed into the 2nd format to create both Line and Bar Charts. In Excel both formats are valid. With the help of a Cross-Tab Query, we can change the data format for our Chart.

1. Create a Table with the Field Names Desc, Date & Val, and save it with the name Stock1.

2. Key in the six records from the sample data above, as we normally maintain data in Access Tables.

3. Display the SQL Window of a new Query (don't select a table or Query from the list displayed), copy and paste the SQL String of a Cross-Tab Query given below into the SQL Window, and save it with the name BarChartQ

   .

   TRANSFORM Sum(Stock1.Val) AS SumOfVal
   SELECT Stock1.Desc
   FROM Stock1
   GROUP BY Stock1.Desc
   PIVOT Stock1.Date;

4. Open a new Report in the design view. Select Object. . ., from Insert Menu (Chart Option on the View Menu uses only six Fields for a Chart), select Microsoft Graph Chart, and Click OK. A Chart Object with default values is inserted into the Report.

5. Click outside the chart on the report to de-select the chart from Edit mode. Click again on the chart to select it, display the property sheet, and change the following values:

   • Size Mode = Zoom.
   • Row Source Type = Table/Query
   • Row Source = BarChartQ
   • Column Heads = Yes
   • Left = 0.3"
   • Top = 0.3"
   • Width = 6.0"
   • Height = 4.0"
6. Double Click on the Chart. The Chart Formatting Toolbar will appear on the top. Select Column Chart from the Chart Type Toolbar Options or Right-Click on an empty area within the Chart and select Chart Type from the Shortcut Menu and select Column Chart.
   

   Click to Enlarge

   In the design view, the Chart object displays a chart with sample data in an Excel-like data Sheet (if not visible double-click on the chart). But, when the Report is a print preview the Chart will appear with the actual data from the Table/Query we have attached as the Source. To see the result of the Source Data in Design View also copy and paste the Source Data into the Excel-like Cells.

   Minimize the Report that we are working on. Click on the Query Tab. Double-Click on the BarChartQ to open it. Click at the left top corner of the Datasheet display to select full data. Click the Copy Toolbar Button or select Copy from Edit Menu. Restore the Report with the Chart and double-click on the Chart to display the Data Grid. Click on the left top corner of the Grid to select all the cells. Click the Paste button on the Toolbar or select Paste from Edit Menu. Click on the left border of the extra two lines below and select cut from Edit Menu to remove the unwanted data. Now the Chart in the Design view displays the actual chart values.

   Chart Formatting for Visually Pleasing Look

   We will format the Chart to give it a better look.

7. If the Chart is still in the same stage when you have pasted the Chart Values in the Grid (otherwise double-click on it to modify the Chart Options) right-click at the center of the Graph where the shaded area is and away from the Chart Bars. The Plot Area is highlighted; select Format Plot Area . . ., from the Shortcut Menu. Put a check-mark on the None option under Area Options and click OK to close the options menu.
8. Right-click on one of the Bars and select Format Data Series . . ., Click on the Fill Effect Button and select Gradient Tab.
9. Click Vertical under Shading Styles and Click on the Right Bottom Style, out of four Styles displayed. If you want to change the Color of the Bars you can select the color of your choice from the color palette. Accept the default One Color Option without change.
10. Select the Data Labels Tab. Put a check-mark in the Value Option and click OK to update the change on the Graph.
11. If the Data Label Font is large then right-click on one of the labels and select Font, Font Style, Size, etc., from the Menu, and change the values to your liking (say 10 points). Repeat the same change in X-Axis Labels, Y-Axis Labels, and Legend.
12. Right-Click on the Chart outside the Plot Area and select Chart Options.
13. Type NYSE Index in the Chart Title Control.
14. Save the Report with the Chart and open it in Print Preview.
    

    Click to Enlarge

15. Add two more records to the Chart's Table Stock1. Print Preview the Report with the Chart again and check whether the newly added records are appearing as new Bars on the Chart.

You can create a Line-Chart from the same Bar Chart. Click on the Chart in Design View, Copy and Paste another instance of the same Chart below the Bar Chart. Double-click on the new Chart to Edit the Chart Options. Right-click on an empty area of the Chart away from the plot area, click on the Chart Type, and select Line 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