Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Wednesday, August 29, 2007

MS-Access and Graph Charts

Introduction

Access Reports are excellent at presenting information in numbers and text. But, Charts go 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, to 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 the preparation of Graphs. To get some familiarity, we will look into some examples that use only one set of values and the data format that is needed for the graph.

Events like the daily change of day, temperature, or change in body temperature, of patients in a hospital, to monitor their condition, or the change of the gold price over a period of time, etc., can be plotted in the form of Line/Bar Charts on 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 on the left side and the dots can be joined by lines. The actual value must be placed near the dot. The 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.

Preparing for a simple Chart

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 show the share of each value to the Total then Pie-Chart will be the 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 the 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, every month, then the data source object like Table or Query must be prepared to keep 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.

Charts in Excel and MS-Access.

Preparing Charts in Access found not as easy as in Excel. We will look into the most commonly used Chart Types: Line, Bar & Pie Chart examples in Access. Those who worked with Excel Charts may find it difficult to get adjusted to the data preparation methods of Access for charts. But when you are through with the initial difficulties I don't think you will go back to Excel anymore.

But, unlike Excel we have the advantage of several types of Queries and Macros, to automate the process, to skim out figures from large volumes of data quickly and effectively, for any type of Chart. Once it is prepared and set it up, it works every time without manual intervention.

Note: You may attach the Microsoft Excel Object Library to the Access Database library Files List so that we can make use of all the Chart Types and formatting capabilities of Excel can be made available in Access also.

Line and Bar Charts

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 our 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 the 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 into 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 the 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 a print preview the Chart will appear with the actual data from the Table/Query we have attached as the 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 on the left border of the extra two lines below and select cut from Edit Menu to remove the unwanted data. Now the Chart in the Design view displays the actual chart values.

    Chart Formatting for Visually Pleasing Look

    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 in 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 to 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.

  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

11 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

Comments subject to moderation before publishing.

Powered by Blogger.