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 the Object option 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 deselect 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. Creating and Formatting a 3-D Pie Chart.

   1. Double-click on the chart control in your report or form to open the Chart Formatting Toolbar.

   2. From the Chart Type Toolbar, select 3-D Pie Chart.
      Alternatively:

      • Right-click on a blank area inside the chart (not on the pie itself).

      • Choose Chart Type from the shortcut menu.

      • Select 3-D Pie Chart and click OK.

   3. Open the Pie_Table:

      • Click on the top-left corner of the datasheet (grid) to select all data.

      • Select Edit > Copy from the menu.

   4. Go back to your chart:

      • Click the top-left corner of the chart's datasheet grid.

      • Select Edit > Paste to paste the data.

   5. Remove extra sample data:

      • If any extra rows or columns remain after pasting, select them and use Edit > Cut to delete.

   6. Resize the Pie:

      • Click on the shaded area surrounding the pie to select it.

      • Drag the bottom-right sizing handle outward to enlarge the pie slightly.

   7. Format the Plot Area:

      • Right-click on the shaded area around the pie.

      • Select Format Plot Area from the shortcut menu.

      • Set Area Options to None.

      • Set Border Options to None, then click OK.

   8. Set Chart Title and Data Labels:

      • Right-click on an empty area of the chart.

      • Select Chart Options.

      • On the Titles tab, enter "Total Revenue" in the Chart Title box.

      • Switch to the Data Labels tab, check the Percentage option under "Label Contains".

      • Click OK to apply your changes.

Click to Enlarge

A 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 using the field structure and data provided above, and save it as Table1. Then, follow the same steps outlined in the earlier post, MS-Access and Graph Charts, starting from Step 4, to create the chart shown below. In Step 5, set the Row Source property to Table1.

The completed Bar Chart, created using the sample data above, illustrates the quarterly performance of individual areas—Revenue, Expenses, and Income—and is shown below.

Click to Enlarge

Customizing Chart Y-axis Scale Values

The Y-axis scale of the chart, along with the major unit intervals (e.g., 0, 5000, 10000), is automatically calculated and displayed by MS Access. However, when the chart data contains smaller values, it may be necessary to adjust these intervals for better visibility. In such cases, you can manually customize the Y-axis scale to use smaller unit intervals as needed.

To adjust the Y-axis scale manually, double-click on the chart to enter edit mode. Then, right-click on the Y-axis (the vertical line displaying the scale values) and select Format Axis from the shortcut menu. In the dialog box that appears, go to the Scale tab and modify the values as needed. For example, you can change the Minimum, Maximum, or Major Unit settings to better suit your chart data.

• 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 manually change the Y-axis scale settings, they will remain fixed, even if the actual chart values exceed the defined maximum. In such cases, you must update the maximum value manually to ensure all data points are displayed correctly. Alternatively, you can enable automatic scaling by checking all relevant options in the Scale tab, allowing MS Access to recalculate and adjust the scale values dynamically as the data changes.

Formatting Data Labels.

You can adjust the alignment of the chart’s data labels to improve readability. Right-click on any label and choose Format Data Labels from the shortcut menu. Go to the Alignment tab and select one of the diamond-shaped icons under Orientation to change the label direction. Feel free to experiment with the other alignment options to find the best fit for your chart layout.

You can display the actual data table used to generate the chart alongside the chart itself. To do this, double-click on the chart to enter edit mode. Then, right-click on an empty area outside the plot area and select Chart Options. Navigate to the Data Table tab and check the Show Data Table option.

Click to Enlarge

Secondary Y-axis Usage.

Sometimes, we need to display smaller values alongside much larger ones on the same chart. For example, if the income values for all four quarters are below 3000, their bars or lines (in a line chart) may appear too small, making it difficult to compare them effectively.

In such scenarios, using a Secondary Y-axis allows you to scale smaller values independently, enhancing the visibility of bars or lines representing those values. Including data labels further improves clarity. The sample image below illustrates this, with Income values (colored bars) plotted on the secondary Y-axis.

Click to Enlarge

Adjusting Bar Width.

To reduce the thickness of the blue bars and make them as narrow as the other bars, you need to increase the gap between them. Double-click the chart to enter edit mode, then right-click one of the blue bars and select Format Data Series. On the Options tab, set the Gap Width value to 340, and click OK to apply the changes to the chart.

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 Objects 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