Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and Graph Charts2

Continued. . .

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 design view. Select Object . . . from Insert Menu, select Microsoft Graph Chart and Click OK. A Chart Object with default values is inserted on 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 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 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, click and drag the right bottom corner sizing control to make the Pie little larger. Right-Click on the shaded area around the Pie and select Format Plot Area, select None from the Area options 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

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

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.

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 check mark on all settings to calculate the scale values automatically by MS-Access.

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 symbol 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 check mark on Show data Table option.

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 3000 mark and the Bars or Lines (in Line Graph) of those set 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:

To reduce the thickness of the Blue Bars to make it 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 Gap-Width Value to 340 in Options Tab and 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:

Share:

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Array Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Calculation Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

MS-Access Class Module and VBA

Last few weeks we have learned how to use User Defined Type (UDT)  by creating a complex data structure and we know UDT’s strength or weakne...

Labels

Blog Archive

Recent Posts