If you have used Microsoft Excel to create charts, you are probably familiar with them and the effects available. While both Microsoft Excel and Microsoft Access base their charts on the same engine, Microsoft Access presents some limitations, especially with formatting or drawing. Therefore, while you should be able to analyze any data in Microsoft Access using charts, because of the way it is setup, you may not have all of the same options available as for Microsoft Excel.

Creating a Chart

Types of Charts

Column Charts

A column chart can render a very effective result when analyzing data of the same category on a defined scale. The column chart emphasizes high and low values. It helps to compare items.

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.

We are going to create a chart to shows us how often a particular container gets ordered. During data analysis, this type of chart can show management what type of container should be supplied more often.

Practical Learning: Creating a Column Chart

1. From the resources that accompany this ebook, copy the Clarksville Ice Scream1 database and paste it in your Exercises folder

2. Start Microsoft Access and open the Clarksville Ice Scream1 database. If necessary, click Queries in the Database window

3. To create a new query, double-click Create Query In Design View.
   If you are using MS Access 97, in the Queries property page of the Database window, click the New button and, in the New Query dialog box, double-click Design View

4. In the Show Table dialog box, double-click CustomersOrders followed by Containers and click the Close button

5. In the Containers list, double-click Container
    

6. To view the result, switch the query to Datasheet View

7. Save the query as ContainerOrderFrequency and close it

8. On the main menu, click Insert -> Form

9. In the New Form dialog box, click Chart Wizard and, in the bottom combo box, select ContainerOrderFrequency
    

10. Click OK

11. In the first page of the Chart Wizard, in the Available Fields list, double-click Container to include it in the Fields For Chart list:
     

12. Click Next

13. In the second page of the Chart Wizard, click the chart in the 2nd column - 1st row 3-D Columnar Chart
     

14. Click Next

15. To take a glimpse at the chart, click the Preview Chart button
      

16. Click Close and click Next

17. Set the Title to Number of Containers Ordered and press Enter

18. Save the form as chtContainersOrdered
     

19. After viewing the chart, close the form

20. Print the chtContainersOrdered form

Bar Charts

Like the Column, the Bar chart is used to compare values of the same category on a common scale. This time, the chart is drawn horizontally. You create a Bar chart using the same process as the Column, except that you should select the Bar in second page of the wizard.

In the following chart, we will see the frequent number of scoops that customers order.

In the following exercise, we didn't have any choice but to violate one of our principles: we are referring to an issue that has not been introduced previously. What happens is that we are going to ask you to use a function that cannot be explained at this time. The function we want you to use is called Choose(). We will explain it when we get to the functions and expressions. The problem is that, the chart we want to create is completely based on numbers from the Scoops column. If we just use the number, Microsoft Access "thinks" that we want to use a sum of the scoops and create a chart from it. Instead, we want to get a sum of scoops by categories. That is, we want to know the number of 1 scoops, or the number of 2 scoops, or the number of 3 scoops.

Therefore, in our own defense, here is what we propose to you. Since the following exercise is for demonstration purposes, you can skip it and you will not loose anything. Otherwise, simply do the exercise and trust us as you have done so far.

Practical Learning: Creating a Bar Chart

1. To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View

2. In the Show Table dialog box, double-click CustomersOrders and click Close

3. In the CustomersOrders list, double-click Scoops and press Tab. That will put the caret in the second column

4. In the second column, type 
   NumberOfScoops: Choose([Scoops], "One", "Two", "Three")
   and press Enter

5. Save the query as TypesOfScoops
    

6. Switch the query to Datasheet View to preview it. Then close it

7. To create a new chart, on the main menu, click Insert -> Form

8. In the New Form dialog box, click Chart Wizard and, in the bottom combo box, select TypesOfScoops and click OK

9. In the first page of the Chart Wizard, in the Available Fields list, double-click NumberOfScoops to include it in the Fields For Chart list:
    

10. Click Next

11. In the second page of the Chart Wizard, click the chart in the 1st column - 2nd row Bar Chart
     

12. Click Next twice

13. Set the Title to Number of Scoops/Order and click Finish

14. Save the form as chtNumberOfScoopsPerOrder
     

15. After viewing the chart, print then close the form

Line Charts

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 very helpful in comparing values of the same category over the same period. The Line chart can also be used to analyze values that don't share the same periodic variable. For example, you can use it to compare library attendance with regard to the real population number (which could be in hundreds of thousands or millions) with the number of people attending the library. In the latter situation, if the same axis are used to analyze, one category will almost disappear from the chart; the alternative is to separate their axis on the same chart.

In the following chart, we are picking a sample week of the year. We then isolate the names of weekdays to evaluate the tendency of customers orders. What we want to know is what days produce more orders and what days are slow. This type of information can help the management decide what days they need more cashiers because there are more customers. On the slow days of the week, the business doesn't need many employees, at least not too many cashiers serving customers.

To prepare data that will be used, we will create a query that uses the dates orders are places. Fortunately, we also have a column that evaluates weekdays. This second column will be the actual source of data for the chart. The first column allows us to specify the criteria, which consists of considering only one week as our sample.

Practical Learning: Creating a Line Chart

1. To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View

2. In the Show Table dialog box, double-click CustomersOrders and click Close

3. In the CustomersOrders list, double-click OrderDate

4. In the Criteria field for the new column, type >=#01/06/2002# And <=#01/12/2002# and press Enter

5. In the CustomersOrders list, double-click DayOfWeek to put it in the second column

6. Save the query as SecondWeekOfJanuary2002
    

7. Switch the query to Datasheet View to preview it. Then close it

8. To create a new chart, on the main menu, click Insert -> Form

9. In the New Form dialog box, click Chart Wizard and, in the bottom combo box, select SecondWeekOfJanuary2002 and click OK

10. In the first page of the Chart Wizard, in the Available Fields list, double-click DayOfWeek to include it in the Fields For Chart list and click Next

11. In the second page of the Chart Wizard, click the chart in the 3rd column - 3rd row Bar Chart
     

12. Click Next twice

13. Set the Title to Daily Orders in a Sample Week and click Finish

14. Save the form as chtSampleDailyOrders
     

15. After viewing the chart, close the form

16. Print the chtSampleDailyOrders form

Pie Charts

A Pie chart is used to show percentage and/or fraction values. You should choose it whenever your analysis involves values that altogether evaluate to a 100%, a 1, a 10, a 100, or a 1000 scale.

To prepare data for a Pie chart, you can select values as you would any other chart. When asked to create the chart, the chart engine would perform the necessary evaluation. It would calculate the sum of all values, then find out what proportion or fraction each category claims in the total.

Practical Learning: Creating a Pie Chart

1. In the Database window, click Queries if necessary.
   To create a new query, double-click Create Query In Design View.
   If you are using MS Access 97, in the Queries property page of the Database window, click the New button and, in the New Query dialog box, double-click Design View

2. In the Show Table dialog box, double-click CustomersOrders followed by Flavors and click the Close button

3. In the Flavors list, double-click Flavor 

4. To view the result, switch the query to Datasheet View

5. Save the query as FlavorOrderFrequencies and close it

6. On the main menu, click Insert -> Form

7. In the New Form dialog box, click Chart Wizard and, in the bottom combo box, select FlavorOrderFrequencies, and click OK

8. In the first page of the Chart Wizard, as the Flavor item is already selected in the Available Fields list, click the select one button to add Flavor to the Fields For Chart list and click Next

9. In the second page of the Chart Wizard, click the chart in the 1st column - 4th row Pie Chart
    

10. Click Next

11. To take a glimpse at the chart, click the Preview Chart button
      

12. Click Close and click Next

13. Set the Title to Frequency At Which A Flavor Is Ordered and press Enter

14. After viewing the chart, save the form as chtFlavorFrequency and close it

Doughnut Charts

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

Practical Learning: Create a Column Chart

1. To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View

2. In the Show Table dialog box, double-click Ingredients followed by CustomersOrders and click Close

3. In the Ingredients list, double-click Ingredient

4. Run the query to preview it

5. Save the query as ChoiceOfIngredient then close it

6. To create a new chart, on the main menu, click Insert -> Form

7. In the New Form dialog box, click Chart Wizard and, in the bottom combo box, select ChoiceOfIngredient and click OK

8. In the first page of the Chart Wizard, click the select one button to add the Ingredient item to the Fields For Chart list and click Next

9. In the second page of the Chart Wizard, click the chart in the last column - last row Doughnut Chart 

10. Click Next twice

11. Set the Title to Customers Choice of Ingredient and click Finish

12. Save the form as chtChoiceOfIngredient
     

13. Print the chtChoiceOfIngredient form

14. After viewing the chart, close the form

The Cylinder, Cone, and Pyramid Charts

The Cone, Cylinder, and Pyramid charts can be used in the same scenario as the column and bar. Their 3-D visual effect can enhance the overall analysis of data.

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.

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

The Pyramid chart resembles the cone chart with a difference on their respective base. Both are constructed the same and can be used in similar scenarios. When analyzing data for geographical purposes, the Pyramid chart can render very dramatic effects.

Practical Learning: Creating a Pyramid Chart

1. To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View

2. In the Show Table dialog box, double-click CustomersOrders and click Close

3. In the CustomersOrders list, double-click DayOfWeek

4. Run the query to preview it

5. Save the query as DailyCustomerAffluence and close it

6. To create a new chart, on the main menu, click Insert -> Form

7. In the New Form dialog box, click Chart Wizard and, in the bottom combo box, select DailyCustomerAffluence and click OK

8. In the first page of the Chart Wizard, click the select one button to add the DayOfWeek item to the Fields For Chart list and click Next

9. In the second page of the Chart Wizard, click the chart in the last column - 1st row Pyramid Column Chart 

10. Click Next twice

11. Set the Title to Daily Customer Affluence and click Finish

12. Save the form as chtDailyAffluence and print it

13. After viewing the chart, close its form

Multi-Series Charts

In some cases you may want to analyze two or more series of data, either concurrently or as they relate to each other. For example, in our ice scream application, you may want to know how often customers who order the Butter Pecan flavor are likely to add an ingredient to their order (since butter pecan already has some nuts in it). Also, if another analysis reveals that Vanilla is most often ordered, is it related to a particular time of the day?

To analyze data from two series, when preparing it, make sure you create a way for the chart engine to figure out how to reconcile the numbers. Also, not all charts are made for multiple series.

Practical Learning: Creating a Multi-Series Chart