MS-Access and Graph Charts
Access Reports are excellent in presenting information in Numbers and Text. But, Charts goes 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: 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 preparation of Graphs. To get some familiarity we will look into some examples that uses only one set of values and the data format that is needed for the graph.
Events like the daily change of day temperature or the change of body temperature. of patients in a hospital, to monitor their condition, or the change of Gold price over a period of time etc. can be plotted in the form of Line/Bar Charts on a 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 at the left side and the dots can be joined with lines. The actual value must be placed near the dot. 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.
We plan to monitor the daily Stock Index and the values are recorded in an Access Table as given below.
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 to show the share of each value to the Total then Pie-Chart will be 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 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, say every month, then the data source object like Table or Query must be prepared keeping 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.
Preparing Charts in Access is not as easy as in Excel. We will look into the most commonly used Chart Types: Line, Bar & Pie Chart examples in Access.
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 the Chart.
- Create a Table with the Field Names Desc, Date & Val and save it with the name Stock1.
- Key-in the six records from the sample data above, as we normally maintain data in Access Tables.
- 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;
- Open a new Report in 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 on the Report.
- 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"
- 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.
In 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 Print previewed the Chart will appear with the actual data from the Table/Query we have attached as 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 at the left border of the extra two lines below and select cut from Edit Menu to remove the unwanted data. Now the Chart in Design view displays the actual chart values.
We will format the Chart to give it a better look.
- 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.
- Right-Click on one of the Bars and select Format Data Series . . . Click on the Fill Effect Button and select Gradient Tab.
- 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.
- Select the Data Labels Tab. Put a check-mark in the Value Option and click OK to update the change on the Graph.
- 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 for X-Axis Labels, Y-Axis Labels and Legend.
- Right-Click on the Chart outside the Plot Area and select Chart Options.
- Type NYSE Index in the Chart Title Control.
- Save the Report with the Chart and open it in Print Preview.
- Add two more records on 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.