How to use chart Wizard in Access

Custom Charts

Introduction

In our introduction to charts, we created one with standing rectangular boxes. This is called a column chart and is only one of the types of charts available. Microsoft Access and Microsoft Excel provide many other flavors you can use, depending on the type of analysis you want to perform.

In our introduction to charts, we created one with standing rectangular boxes. This is called a column chart and is only one of the types of charts available. Microsoft Access and Microsoft Excel provide many other flavors you can use, depending on the type of analysis you want to perform. When starting a (new) chart, in the third page of the wizard, you can select the type of chart you want. After the chart has been created, you may prefer another type of chart. In most cases, you can change the type of chart, easily. A chart created with all default settings usually accomplishes its purpose of helping you analyze data and figures, but the default features are set only as starting points. All the formatting and emphasis needs are left up to you.

The Chart Wizard in Microsoft Access is equipped with various types of charts. In each category, different sub-types are used to accomplish a unique purpose. In Microsoft Access, to change a chart, after displaying its parent form or report in Design View, you can double-click it. This would open the Microsoft Graph application. From there, you can right-click any part of the chart to make the necessary changes. Any area you right-click presents a particular menu.

In Microsoft Access, to change the type of chart, right-click an area of the chart and click Chart Type. This would open the Chart Type dialog box. It presents the various available types of charts and you can click one of them. If none of the available types suits you, you can click the Custom Type tab for more options.

The Types of Values of a Chart

When you select the values of a table or a query and ask Microsoft Graph to use them to create a chart, by default, the application counts the number of occurrences of each value, especially if you select a string-based field. Depending on the type of chart as we will see in the next few sections, some charts can use regular numbers while some others are better with percentage values. Fortunately, instead of trying to figure out how to perform the calculations yourself, Microsoft Graph can do it for you.

Bar Charts

A bar chart uses the same theories and scenarios as the column chart except that its rectangular boxes are horizontal. Like the column chart, the bar chart is used to compare values of the same category on a common scale. You create a bar chart using the same approach as the column chart, except that you should select the Bar Chart in second page of the wizard:

As done for the column chart, when specifying the values of a bar chart, use a column that has frequent occurrences of the same values.

1. Start Microsoft Access and open the Altair Realtors4 database from the previous lesson
2. On the Ribbon, click Create
3. In the Forms section, click Form Design
4. In the Controls section of the Ribbon, click the Chart button
   
5. Click and drag on the form from the top-left side to to somewhere horizontal ruler 8-mark and vertical ruler 5-and-half-mark
   

6. Release the mouse
7. In the first page of the Chart Wizard dialog box, make sure the Tables radio button is selected and, in the list, click Table: Properties
8. Click Next
9. In the second page of the Chart Wizard, in the Available Fields list, double-click Condition to include it in the Fields For Chart list
   

10. Click Next
11. In the third page of the Chart Wizard, click Bar Chart
    

12. Click Next
    

13. Click Next
14. Set the Title to Properties Conditions
15. Click No, don't display a legend
16. Click Finish
17. Right-click an unoccupied area of the chart, position the mouse on Chart Object, and click Open
18. Right-click the title and click Format Chart Title...
19. In the Format Chart Title dialog box, click the Font property page and change the following characteristics: Font: Calisto MT (if you don't have that font, select Times New Roman) Size: 18

    Color: Red

20. Click OK
21. Right-click any label on the left (North, West, or East) and click Format Axis
22. In the Format Axis dialog box, click Font and change the following characteristics: Font: Calisto MT (if you don't have that font, select Times New Roman) Font Style: Regular Size: 11

    Color: Blue

23. Click OK
24. Close Microsoft Graph
25. Switch the form to Form View
    

26. Close the form without saving it

Column Charts

As we have seen already, a column chart creates vertically standing rectangular boxes. Each box can be used to represent an integral, a decimal, or a percentage value. When creating such a chart, you specify the values to use. Microsoft Graph determines the highest and the lowest values. When the boxes are drawn, each one of them must fit in the area allocated for the chart. As a consequence, the box that represents the highest value is also the tallest while the box for the lowest value is the shortest. Microsoft Graph draws the other boxes between these extremes but proportionately. Therefore, a column graph is used to compare values in increment.

Double-Column Charts

The classic column chart is made of flat bars that simply illustrate maximal, minimal, and in-between values. One of the options allows you to create a 3-dimensional look of the chart and further accentuate the colors and/or other graphic effects. To enhance an effective analysis, you can create a real 3-D chart that shows data and graphics in perspective.

Another variance of the column chart is to show two columns for each sample value.

1. On the Ribbon, click Create and click Query Design
2. In the Show Table dialog box, double-click Properties and click Close
3. In the list of items, double-click PropertyNumber, MarketValue, and SaleValue
4. In the bottom side of the window, click the Criteria box for SaleValue and type Is Not Null
5. Switch the query to Datasheet View to preview it
6. Save the query as Sales Summary and close it
7. On the Ribbon, click Create and click Form Design
8. Enlarge the report up to measure 10 on the horizontal ruler
9. In the Controls section of the Ribbon, click the Chart button
   
   and click the area under the Detail bar
10. In the first page of the Chart Wizard dialog box, click the Queries radio button
11. In the list, click Query: Sales Summary
12. Click Next
13. In the second page of the Chart Wizard, click the button to select all 
    
14. Click Next
15. In the third page of the Chart Wizard, accept the default Column Chart and click Next
16. In the fourth page of the wizard, from the right side, drag SaleValue and drop it on the top-left box on the chart
    
    

17. Click Next
18. Accept the suggested title and click No, don't display a legend
19. Click Finish
20. Display the form in Form View
    

21. Close the form without saving it

Pie Charts

A pie chart is used to show percentage and/or fractional values. When creating it, you can choose the values. Microsoft Graph would identify each value in the column and create categories for them. After getting the categories, the application would calculate the percentage for each category based on the sum of all the values, the total count of categories, and the fraction that each category shares.

The default appearance of a pie chart is a circle with each category taking a pie in the whole. One of the variances of the chart displays in three dimensions that use two ellipses. The top ellipse is the most visible and shows the format of each chart. Only part of the bottom ellipse is shown.

1. On the Ribbon, click Create and click Form Design
2. In the Controls section of the Ribbon, click the Chart button
   
3. Click the form
4. In the first page of the Chart Wizard dialog box, click the Tables radio button.
   In the list, click Table: Properties
5. Click Next
6. In the second page of the Chart Wizard, in the Available Fields list, double- click Property Type and click Next
7. In the third page of the Chart Wizard, click Pie Chart in the 1st column - 4th row
   

8. Click Next
9. Click Next
10. Set the Title to Distribution of Properties Types
11. Click No, don't display a legend
12. Click Finish
13. Right-click the chart, position the mouse on Chart Object, and click Open
14. In Microsoft Graph, right-click the chart and click Format Data Series
15. In the Format Data Series dialog box, click the Data Labels tab
16. Click Category Name and click Percentage
    

17. Click OK
18. Format the other parts any way you want and close Microsoft Graph
19. Display the form in Form View
    

20. Preview, print, save, and close the form

Doughnut Charts

A Doughnut chart is an alternative to the Pie chart as both use the same types of values. The main difference between both types of charts is that a Doughnut chart can include more than one series of values.

3-D Column Charts

Introduction

So far, we have used what are referred to as flat charts. They can be drawn on a 2-dimensional coordinate system (x, y). To enhance the appearance of a chart, you can draw it in 3-dimensional coordinate system (x, y, z). If you want to draw a 3-D chart, you must select three columns. Two of the columns should hold categories of values and the other one can hold unique values. The two columns that hold categories of values should have corresponding values so that a value from one column can have corresponding values in the second column. Here is an example. Imagine that, in a real estate database, you have been selling properties over a period of 1, 2, 3 or more years. The properties sold are categorized as single families, townhouses, and condominiums. Obviously in a particular year, you sell properties of all kinds. On the other hand, each property can have its own value. You can use these three sets of values to create a 3-D chart. The cone, cylinder, and pyramid charts can be used in the same scenario as the column chart. Their 3-D visual effect can enhance the overall analysis of data.

When creating the chart, there are many aspects you can change for it. For example, you may have a chart where the figures in the front seem to hide those in the back. Here is an example:

Or there is too much room on one side. You can rotate the chart. To do this, click one of the borders of the walls of the chart to select its frame. Then click one of the handles on the frame and hold the mouse down. The actual frame of the chart would appear:

You can then rotate the chart in the direction of your choice. You can keep doing this, releasing the mouse to preview, then rotating again, until you get the desired orientation.

If you created the chart as one shape (cylinder, cone, or pyramid) but want to use another shape, you can change it. To do this, in Microsoft Graph, right-click the chart and click Format Data Series. In the Format Data Series dialog box, click the Shape tab and select a different shape:

Cylindric Charts

The cylinder chart creates long circular boxes of the same base on both ends. It can be enhanced with good formatted Fill Effects. This chart is suitable for industry, manufacturing analysis, and predictions.

1. On the Ribbon, click Create and click Query Design
2. In the Show Table dialog box, double-click Properties and click Close
3. On the Ribbon, click the Totals button
   
4. In the list of items, double-click PropertyType and Condition
5. In the bottom side of the window, click the Total box for the Condition column, then click the arrow of its combo box and select Count
6. In the Properties list, double-click Condition
   

7. Switch the query to Datasheet View to preview it
   

8. Save the query as Properties by Conditions and close it
9. On the Ribbon, click Create and click Form Design
10. In the Controls section of the Ribbon, click the Chart button
    
    and click the form
11. In the first page of the Chart Wizard dialog box, click the Queries radio button and, in the list, click Query: Properties by Conditions
12. Click Next
13. In the second page of the Chart Wizard, click the button to select all 
    
    and click Next
14. In the third page of the Chart Wizard, click the Cylinder Column Chart
    

15. Click Next
    

16. Click Next
17. Accept the suggested title and click No, don't display a legend
18. Click Finish
19. Right-click the chart, position the mouse on Chart Object, and click Open
20. Change the font sizes to make them smaller
21. Display the form in Form View
    

22. Notice that the values in the back side of Condominiium are difficult to see.
    Switch the form to the Design View
23. Right-click the chart, position the mouse on Chart Object, and click Open
24. Right-click an unoccupied area of the chart and click 3-D View
25. In the 3-D View dialog box, click the button to rotate to the right
    

26. Click Close
27. Close Microsoft Graph
28. Display the form in Form View
    

29. Switch the form to Design View

Conic Charts

The cone chart is made of a circular base topped by a higher point. When used with various data, the higher value will have the complete cone while the lower values will share portions of the geometric figure. The cone chart should be used with values that can take advantage of its graphing dimensions.

1. Right-click the chart, position the mouse on Chart Object, and click Open
2. In Microsoft Graph, right-click the chart and click Chart Type...
3. In the Chart Type dialog box, in the Chart Type list, click Cone
4. In the Chart Sub-Type list, click 3-D Column With a Conical Shape
   

5. Click OK
6. Right-click an unoccupied area of the chart and click 3-D View
7. In the 3-D View dialog box, change the value of the Rotaion text box to 12
8. Click Close
9. Close Microsoft Graph
   

10. Switch the form to Design View

Pyramid Charts

The pyramid chart resembles the cone chart with a difference on their respective bases. Both are constructed the same and can be used in similar scenarios.

1. Right-click the chart, position the mouse on Chart Object, and click Open
2. In Microsoft Graph, right-click the chart and click Chart Type...
3. In the Chart Type dialog box, in the Chart Type list, click Pyramid
4. In the Chart Sub-Type list, click 3-D Column With a Pyramic Shape
   

5. Click OK
6. Close Microsoft Graph
   

7. Switch the form to Design View

Line Charts

Introduction

A line chart is used to analyze ups and downs of a tendency in a range of values. You can define it with one series of values where you will judge the evolution of an item over a period. When used with more than one series, this chart can be helpful in comparing values of the same category over the same period. The line chart can also be used to analyze values that do not share the same periodic variable.

1. On the Ribbon, click Create and click Query Design
2. In the Show Table dialog box, double-click Properties and click Close
3. On the Ribbon, click the Totals button
   
4. In the bottom side of the window, click the first empty Field box and type DateSold: Format([SaleDate],"mmm yyyy") and press Tab
5. In the list of items, double-click MarketValue and SaleValue
6. In the bottom side of the window, set the Total values of MarketValue and SaleValue to Sum each
7. Set its Criteria of SaleValue to is not null
8. Click the Show box of the last column to remove the check mark
   

9. Switch the query to Datasheet View to preview it
10. Save the query as Monthly Sales and close it
11. On the Ribbon, click Create and, in the Forms section, click Form Design
12. In the Controls section of the Ribbon, click the Chart button
    
    and click the area under the Detail bar
13. In the first page of the Chart Wizard dialog box, click the Queries radio button
14. In the list, click Query: Monthly Sales and click Next
15. In the second page of the Chart Wizard, click the button to select all 
    
    and click Next
16. In the third page of the Chart Wizard, click the Line Chart in the 3rd column - 3rd row
    

17. Click Next
18. In the fourth page of the wizard, drag SumOfSaleValue and drop it in the box in the top-left side of the chart
    

19. Click Next
20. Accept the Title as Monthly Sales and click No, don't display a legend
21. Click Finish
22. Right-click a white area in the chart, position the mouse on Chart Object, and click Open
23. Format the different sections as you see fit and close Microsoft Graph
24. On the Ribbon, click the Page Setup tab and click the Landscape button
25. Save the report as FY 2008 Home Sales Per Month and display it in Print Preview.
    If you want, switch the report back to Design View and format the chart as you see fit
    

26. Save and close the report

Trend Lines

A trend line is a line added to a chart created as a column or else. It can be used to show the high points of the various values on a chart. A trend line is not a type of chart. It is only a line added to an existing chart to accentuate its tendencies.

To add a trend line to a chart, after opening Microsoft Graph, right-click one of the column categories and click Add Trendline...

This would open the Add Trendline dialog box. In the Type property page, you can select the type of line you want:

If your chart is using more than one category, you can select each in the Based On Series list box and specify its trend line. The Options property page allows you to specify more options. Once you have finished, click OK:

After creating a trend line, you can change its characteristics. To do this, right-click the trend line and click Format Trendline... This would open the Format Trendline dialog box that you can use for various reasons, including specifying the color of the line. You can also access its Type and its Options property pages and change the original settings. Once you click OK and close Microsoft Graph, the trend line or its new changes would apply to the chart. In the same way, you can add a trend line for each category of the column or bar chart.