Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Wednesday, September 5, 2007

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.

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.

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

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.

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:

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:

  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

2 comments:

  1. Hey there,

    How we can show highlighted line for "Upper Control and Lower Control limits" in access bar Graphs?

    thanks

    zee

    ReplyDelete
  2. Your query is not clear. If you are trying to set the Upper, Lower and Interval values of the Y-Axis Scale (values shown at the left-side vertically) then it is already explained in the Article above. I will copy that part here so that you can go through it:

    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 on 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 to the scale values) and select Format Axis option to display the Menu. Select Scale Tab and change the values as follows:



    Minimum = 0
    Maximum = 51000
    Major Unit = 3000

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


    If you are trying to show a Trend-Line:

    Open the Report or Form with the Chart in Design View
    Double-Click on the Chart to change to Edit Mode
    Click on one of the Bar Series to select them
    Right-Click on one of the selected Bars to display a Shortcut Menu
    Select Add Trend Line... Option

    ReplyDelete

Comments subject to moderation before publishing.

Powered by Blogger.