Lessons Logo

Data Analysis With Charts

 

Overview of Charts

 

Introduction

Introduction to Charts

A chart is a technique of displaying data using pictures and graphical representations instead of numbers or simple words. It works by drawing figures that would represent numbers, giving a dramatic effect to the information presented. Good created and formatted charts can help people and businesses make decisions based on the impact that their images provide to the users.

While data analysis as we have seen so far was performed on records displayed on sheets or forms, data analysis on charts is done using graphics that present pictures. In addition to the pictures, you can add words, also called labels to indicate what the pictures represent.

Because a chart is used to present data in a graphical format, before creating a chart, you should plan it. That is, you should prepare it. There are two pieces of information you should have before starting: The numbers that you want to represent and the type of chart you want to use.

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

The information used to create a chart is the one you would have stored in a table. In some other cases, you can use a query as queries do a good job at isolating records or counting them. Therefore, before creating a chart, you should prepare it so it can be easily recognizable. Data used on a chart can be made of natural numbers. In some other cases, you may want to use percentages. You can also present a series of repeating words and let the chart engine count the occurrences of such words before using them as numbers.

Once the data is ready, to create a chart, display the New Form dialog box and in it, select the Chart Wizard. This would give you is selecting the information needed for analysis followed by the type of chart you want to use. There are different types of charts, ranging from columns to pies, from lines to surfaces, etc, as we will review them.

To present its information more efficiently, a chart is made of different sections. The main area allows users to view the graphical display of data. A legend explains the meaning of various colors on the chart. A title indicates what the chart is used for.

Types of Charts

 

Column Charts

Column Chart

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.

dddd

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.

Cone Chart

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

  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 followed by Containers and click the Close button

  3. In the CustomersOrders list, double-click PeriodOfDay 

  4. In the Containers list, double-click Container

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

  6. Save the query as ContainerByPeriodOfDay and close it

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

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

  9. In the first page of the Chart Wizard, click the select all button to add both fields to the Fields For Chart list and click Next

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

  11. Click Next twice

  12. Set the Title to Container by Period of Day and press Enter

  13. After viewing the chart, save the form as chtContainerByPeriodOfDay

  14. Print the form and close it

 

 

Previous Copyright 2002-2007 Yevol Next