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 |
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.
Create a Table with the Field Names Desc, Date, and Val, and save it with the name Stock1.
Key in the six records from the sample data above, as we normally maintain data in Access Tables.
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;
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.
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"
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.
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.
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:
-
Right-click at the center of the chart, specifically in the shaded background away from the chart bars or lines.
-
When the Plot Area is highlighted, select Format Plot Area… from the shortcut menu.
-
In the Format Plot Area dialog:
-
Go to the Area Options section.
-
Select the None option to remove the background fill.
-
-
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.@@@
-
Right-click on one of the Bars and select Format Data Series, click on the Fill Effect Button, and select the Gradient Tab.
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.
Select the Data Labels Tab. Put a check mark in the Value Option, and click OK to update the change on the Graph.
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.
Right-click on the Chart outside the Plot Area and select Chart Options.
Type NYSE Index in the Chart Title Control.
- Save the Report with the Chart and open it in Print Preview.
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:
-
In Design View, click on the existing Bar Chart to select it.
-
Press Ctrl+C to copy, then Ctrl+V to paste a duplicate chart below the original.
-
Double-click on the new chart to open it for editing.
-
Right-click on an empty space inside the chart area (but outside the plot area) to bring up the shortcut menu.
-
Choose Chart Type from the menu.
-
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.
Very helpful, thank you!
ReplyDeleteVery 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.
ReplyDeleteLink Building Services...
ReplyDeleteBest Link Building Services...
ScrapeBox - Real SEO or Spam?...
ReplyDelete[...Details from this post have infuenced a new post...]...
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.
ReplyDeleteThough 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
Thank you for pointing out the mistake. I have corrected the field name now.
ReplyDeleteTo 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.
You can download a sample database on all chart types from the following link:
ReplyDeletehttp://www.msaccesstips.com/downloads/2008/06/workingwith_chartobject.php
Chart Demo Download Link:
DeleteChartAssistant
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.
ReplyDeleteGood information. I particularly like your explanation of updating the datasheet of the chart.
ReplyDeleteYou 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.
[...] 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