MS-Access and Graph Charts2

Continuation of Last Week's discussion

If you have landed straight on this page please go through the earlier Post MS-Access & Graph Charts and then continue.

Sample Data for Pie-Chart

Desc	Veh Sales	Parts Sales	Service Sales
Total Sales	450000	645000	25000

1. Create a Table with the above structure and data and save it with the name pie_Table.
2. Open a new Report in the design view. Select Object . . ., from the Insert Menu, select Microsoft Graph-Chart, and then Click OK. A Chart Object with default values is inserted into the Report.
3. Click outside the chart on the report to de-select the chart and to come out of 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 = Pie_Table
   • Column Heads = Yes
   • Left = 0.3"
   • Top = 0.3"
   • Width = 6.0"
   • Height = 4.0"
4. Double Click on the Chart. The Chart Formatting Toolbar will appear on the top. Select a 3-D Pie Chart from the Chart Type Toolbar Options or Right-Click on an empty area within the Chart, click on the Chart Type option from the Shortcut Menu, and select a 3-D Pie Chart.
5. Open the Pie_Table, click at the left top corner of the Grid and select Copy from Edit Menu.
6. Click on the top left corner of the Chart Datasheet and Paste the copied value into the Grid.
7. Delete the extra rows or columns of the sample data left in the Datasheet.
8. Click on the shaded area around the Pie to select it, then click and drag the right bottom corner sizing control to make the Pie a little larger. Right-Click on the shaded area around the Pie and select Format Plot Area.  Select None from the Area options list, and select None under the Border Options.
9. Right-click on an empty area of the chart and select Chart Options from the Shortcut Menu.
10. Select Title Tab and type Total Revenue in the Chart Title control.
11. Click on the Data Labels tab and select Percentage from Label Contains options and click OK to update the Chart Options.

Click to Enlarge

Chart with more than one set of Bars

Table1

Desc	Qtr1	Qtr2	Qtr3	Qtr4
A_Revenue	25000	35000	20000	40000
B_Expenses	15000	20000	13000	17000
C_Income	10000	15000	7000	23000

Create a Table with the above Field Structure and Data and save it with the name Table1. Go through the same procedure that we have used in the earlier Post MS-Access and Graph Charts from Step 4 onwards to create the Chart below. In Step-5 change the Property Row source = Table1.

Given below is the Finished Bar Chart created with the above sample data, for monitoring the Quarterly performance of individual Areas: Revenue, Expenses, and Income.

Click to Enlarge

Customizing Chart Y-Axis Scale Values

The Y-Axis Scale of the Chart and the Major Unit Value intervals (0, 5000, 10000, etc.) are also calculated automatically by MS-Access and displayed on the Chart. There are times that we need to modify the Y-Axis Scale Major Unit intervals to smaller values when there are smaller values of the Chart items. We can customize the Scale Values if we need smaller units on the Scale.

Double-Click on the Chart to edit it. Right-click on the Y-Axis Line (the vertical line near the scale values) and select the Format Axis option to display the Menu. Select Scale Tab and change the values as follows:

• Minimum = 0
• Maximum = 51000
• Major Unit = 3000

Click to Enlarge

Leave the other values unchanged. Click OK to update the new scale settings on the Chart.

Once you change these settings manually it remains unchanged even if the actual value of the Chart crosses the Maximum value settings on the Scale. When it does, then you must change the value manually to show the Chart Values correctly or put a checkmark on all settings to calculate the scale values automatically by MS-Access.

Formatting Data Labels.

You can change the Alignment of the Chart Data Labels. Right-click on one of the Labels and select Format Data Labels from the Shortcut Menu. Select the Alignment Tab and click on one of the diamond-like symbols in the Orientation options. Experiment with the other options on the Alignment Tab.

We can display the actual data table Values that we have used for the Chart along with the Chart. Double-Click on the Chart. Right-click on an empty area of the Chart, away from the plot area, and select Chart Options. Select the Data Table Tab and put a checkmark in the Show Data Table option.

Click to Enlarge

Secondary Y-Axis Usage

There are times that we have to use very small values along with very large values on the same Chart. Assume that the Income Values on the above Chart in all four Quarters are less than the 3000 mark and the Bars or Lines (in Line Graph) of those sets of values may not show big enough to compare them properly.

In such situations, we can use the Secondary Y-Axis to calculate the scale of the smaller values and the visibility of the Bars or Lines will be good on the Chart. The presence of Data Labels on the Graph is very important. A sample Image of the above Chart with the Income Values (Blue Colored Bars) plotted on the Secondary Y-Axis is given below:

Click to Enlarge

Adjusting Bar Width

To reduce the thickness of the Blue Bars to make them as narrow as the other Bars we have to increase the gap between Bars. Double-Click on the Chart to invoke edit mode. Right-click on one of the Blue Bars.  Select Format Data Series, and change the Graph Width Value to 340 in the Options Tab. Click OK to update the change on the Graph.

The Image of a Chart plotted with the same values in Custom Chart Type Tubes is given below:

Click to Enlarge

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

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldEditEdit in GingerEdit in Ginger×