MS-Access and Graph Charts

Access Reports and Graph Charts.

Access Reports are a powerful tool for presenting information in both numbers and text. However, Graph Charts take it a step further by conveying data visually, often revealing insights at a glance. With a basic understanding of how charts work, you can easily create them in Access. The key principle is to illustrate the progression or change of an event over time using dots and lines that reflect corresponding values, instead of relying solely on raw numbers or text.

Before creating graphs in MS Access, it's essential to understand how to structure your data appropriately. To get familiar with this process, we'll explore a few examples that use a single set of values and demonstrate the required data format for generating meaningful graphs.

Events such as daily temperature changes, fluctuations in a patient's body temperature (for health monitoring in hospitals), or variations in gold prices over time can all be effectively visualized using line or bar charts. Traditionally, these are plotted manually on graph paper by marking each data point according to a fixed scale, starting from zero at the bottom of the Y-axis (vertical axis), which is calibrated based on the highest expected value.

Each day's data point is plotted as a dot along the Y-axis, aligned with the corresponding date on the X-axis (horizontal axis). These dots can be connected with lines to show trends, and the actual values can be labeled near each dot. A legend is typically included to describe what the graph represents (e.g., Temperature).

Instead of using paper and pencil, we can achieve the same results—more efficiently and professionally—using the Microsoft Graph Chart object in MS Access.

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

Click to Enlarge

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 want to present a chart showing the company's total revenue from various sources—such as Vehicle Sales, Parts Sales, and Service Sales—and highlight each category's contribution to the overall total, a Pie Chart would be the ideal choice.

However, if we want to display the monthly performance of each category separately over time, a Line Chart or Bar Chart would be more appropriate, as they are better suited for showing trends and comparisons across periods.

If the source data for a chart changes at regular intervals—such as every month—then the underlying Table or Query should be designed to automatically reflect the updated data. This ensures that the chart dynamically displays the latest information without requiring manual modifications each time the data changes.

Charts in Excel and MS-Access.

Creating charts in Access isn't as straightforward as it is in Excel. In this section, we’ll explore the most commonly used chart types in Access: Line, Bar, and Pie charts. If you're familiar with Excel charts, you might initially find Access’s data preparation methods a bit challenging. However, once you get past the learning curve, you will find Access charts just as powerful—and might not feel the need to return to Excel at all.

However, unlike Excel, Access offers the advantage of using various types of Queries and Macros for automating the chart data preparation process. This allows us to extract and present key figures from large volumes of data quickly and efficiently, regardless of the chart type. Once the chart setup is complete, it can run consistently without requiring manual intervention.

Note: You can enhance charting capabilities in Access by attaching the Microsoft Excel Object Library to the Access database's list of References. This enables the use of Excel’s rich variety of chart types and advanced formatting options directly within Access.

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, and 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 the 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 deselect 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 at 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.

   

   Click to Enlarge

   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 print-previewed, 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 you're working on and switch to the Queries tab.
   Double-click BarChartQ to open it.
   In the Datasheet view, click the top-left corner to select all records, then click the Copy button (or choose Edit > Copy).

   Restore the Report containing the Chart and double-click the Chart to open its Data Grid.
   Again, click the top-left corner of the Grid to select all cells, and paste the copied data (use the Paste button or Edit > Paste).

   To remove any extra rows, click the left border of the unwanted lines below the data and choose Edit > Cut.

   The Chart in Design View will now reflect the actual data values.

   Chart Formatting for a Visually Pleasing Look.

   We will format the Chart to give a better appearance.

7. Removing the Plot Area Shading for a Cleaner Look.

   If the Chart is still open after pasting the data into the Data Grid (or double-click the Chart to enter editing mode), follow these steps:

   1. Right-click at the center of the chart, specifically in the shaded background away from the chart bars or lines.

   2. When the Plot Area is highlighted, select Format Plot Area… from the shortcut menu.

   3. In the Format Plot Area dialog:

      • Go to the Area Options section.

      • Select the None option to remove the background fill.

   4. Click OK to apply the changes and close the dialog.

   This will remove the shading behind your data series, resulting in a cleaner, more professional-looking chart.@@@

8. Right-click on one of the Bars and select Format Data Series, click on the Fill Effect Button, and select the Gradient Tab.

9. Click Vertical under Shading Styles and click on the Right Bottom Style, out of the 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 size 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.
    

    Click to Enlarge

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.

Creating a Line Chart from the Existing Bar Chart

You can easily create a Line Chart using the same data as your Bar Chart by duplicating and modifying it:

1. In Design View, click on the existing Bar Chart to select it.

2. Press Ctrl+C to copy, then Ctrl+V to paste a duplicate chart below the original.

3. Double-click on the new chart to open it for editing.

4. Right-click on an empty space inside the chart area (but outside the plot area) to bring up the shortcut menu.

5. Choose Chart Type from the menu.

6. In the list of available chart types, select Line Chart and click OK.

Now, your new chart will display the same data in a line chart format.

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