Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

Desc Date Val
Stock Index 01-08-07 1245
Stock Index 02-08-07 1455
Stock Index 03-08-07 1395
Stock Index 04-08-07 1575
Stock Index 05-08-07 1125

 

OR

 

Desc 01-08-07 02-08-07 03-08-07 04-08-07 05-08-07
Stock Index 1245 1455 1395 1575 1125

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.

  1. Create a Table with the Field Names Desc, Date & Val and save it with the name Stock1.
  2. Key-in the six records from the sample data above, as we normally maintain data in Access Tables.
  3. 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;
  4. 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.
  5. 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"
  6. 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.

  7. 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.
  8. Right-Click on one of the Bars and select Format Data Series . . . Click on the Fill Effect Button and select Gradient Tab.
  9. 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.
  10. Select the Data Labels Tab. Put a check-mark in the Value Option and click OK to update the change on the Graph.
  11. 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.
  12. Right-Click on the Chart outside the Plot Area and select Chart Options.
  13. Type NYSE Index in the Chart Title Control.
  14. Save the Report with the Chart and open it in Print Preview.
  15. 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.

Share:

10 comments:

  1. Very creative content. I just discovered your blog site and wanted to say that i have really loved reading through your blog posts. Any way I will be signing up to your food and I hope you publish again rapidly.

    ReplyDelete
  2. Link Building Services...

    Best Link Building Services...

    ReplyDelete
  3. ScrapeBox - Real SEO or Spam?...

    [...Details from this post have infuenced a new post...]...

    ReplyDelete
  4. Thank you, thank you thank you for this post. I was referred to this site by someone on the Utter Access site when I reached out as my attempts were failing so badly. Perhaps there is not much interest in Access graphs or perhaps the Access graphs are so cumbersome compared to Excel that people gave up on them, but I believe that getting Access graphs directly in an Access report would be very useful, especially now that I need two: one a column graph and the other a pie graph.

    Though I have worked with Access queries and reports for a number of years, I had not ever worked with Access graphs. I also plan to comment on my pie adventure on you pie post as some very weird things are happening there with my values that I need to display as percents, but I will leave that for later.

    I followed your directions above, step by step, using your sample data and your crosstab query(by the way your cross tab says Sum(Stock1.Val) but your data says "Value" which caused an error until I fixed the field name so you might want to make the field names consistent. It worked.

    Then I used my own data and the column graph is pretty good, except that the 3 dimensional one does not label each column (labels every other one),but the flat one does. Also the 3 d one puts the values right on the bars instead of on top so they are had to read, but the flat one looks find. Is there a trick to handle these issues as the firm I work for uses the 3d ones in Excel so I would rather use 3 d if I could get the labels and values to work properly.

    Also is there a trick to get more then 6 columns to show? I can get my seven columns to show in the demo in design view but in report view one does not show. My firm has a graph with seven columns so I would like to do the same.

    Thanks again.

    Leah

    ReplyDelete
  5. Thank you for pointing out the mistake. I have corrected the field name now.

    To place the Data Labels on the top, click on one of the labels to select the labels(see that all the labels on the series is selected, if you click on the label one more time then only that label will be selected). Right-click on one of the selected labels to display a shortcut menu. Select Format Data Labels option and select the required position of labels from the drop-down menu. You can make the labels to rotate a certain angle by dragging the text meter (right side) to a certain position.

    ReplyDelete
  6. You can download a sample database on all chart types from the following link:

    http://www.msaccesstips.com/downloads/2008/06/workingwith_chartobject.php

    ReplyDelete
  7. Thank you. I was able to move the all seven of the values to be more readable, but not all of the column names at the bottom show for some reason in the 3 D view, only 4 out of 7 show. That seems rather strange. I think the Microsoft people need to spend a little more time on Access charts to make it as straight forward as Excel. Also would be nice if there was an easy way to get all reports into Powerpoint in an automated way. As it is I need to Pdf's and attach as objects or paste snapshots from them. I only have 5 pages but there should be a more friendly way to get this into Powerpoint. Looked at various articles on this and so far seems there is no straight forward way to do this.

    ReplyDelete
  8. Good information. I particularly like your explanation of updating the datasheet of the chart.

    You shouldn't need to use a crosstab as the Row Source of the chart. I created a similar chart in the Northwind sample database with a Row Source of:

    SELECT Orders.[Order Date], Sum([Order Details].Quantity) AS Qty
    FROM Orders LEFT JOIN
    [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]
    GROUP BY Orders.[Order Date];

    There are a couple of icons in chart design that are "By Column" and "By Row". I selected the By Row and my chart appears as expected with creating a crosstab.

    ReplyDelete
  9. [...] value and suitable for PIE Chart. Check the following links for examples of creating Charts: LEARN MS-ACCESS TIPS AND TRICKS - MS-Access and Graph Charts LEARN MS-ACCESS TIPS AND TRICKS - MS-Access and Graph Charts2 __________________ [...]

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

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 Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Copy Custom Functions Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields 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

Function Parameter ByVal and ByRef Usage

Before taking up the above subject let us look at some fundamentals on variables for the benefit of novices. When we define a variable in VB...

Labels

Blog Archive

Recent Posts