Tutorial 8 : Sharing, Integrating, and Analyzing Data

In this tutorial you will learn how to import, export, link, and embed data.  You will also be introduced to the charting, PivotTable, and PivotChart features of Access.  You will see that exporting data as HTML or XML files allow you to view data from your database via an internet browser which can provide visibility to users who do not have access to the actual database.  Sometimes you may have existing data that you need to include in your database, instead of entering all of the data manually you can import the data to reduce entry errors and decrease time.  This data can be imported from an Excel spreadsheet, a CSV (comma separated value) file, or text file directly into an existing Access table, or a new one.  When importing data you want to make sure you reduce redundancy, and the Table Analyzer can help you do just that.  The table analyzer splits a single table into two or more tables if it finds redundant data.  This process is called normalization, and the table analyzer ensures that your tables are in 3NF (third normal form - see the additional resources for information about 3NF).

By the end of this tutorial you will know how to:

  • Export and view an Access table as an HTML file
  • Import a CSV file as an Access table
  • Use the Table Analyzer to ensure 3NF (third normal form)
  • Import and Export XML files
  • Save and run Import & Export specifications
  • Link data from an Excel file
  • Create a multi-page form using a tab control
  • Embed a chart in a form
  • Create & modify PivotTables & PivotCharts

The below resources are provided as additional information that will help you understand the concepts introduced by the textbook.

Textbook pg. ACA15-ACA21
  • Ok, this isn't a link to a website, but it is really great information for understanding the Normalization process that takes place when you use the Table Analyzer. If I had it as an electronic copy I would give it to you, but unfortunately I don't so you'll have to read it from your textbook.
    (Sorry for more reading)
http://bit.ly/Rs9Bu1
  • This article shows you what kinds of data you can import and export in Access, and shows you the basic steps to get started with an import or export operation.
  • BONUS: http://bit.ly/RYFr4d This tutorial from FunctionX also provides insight about the import and export capabilities of Access. This article provides information on exporting XML files and importing from Excel files.
http://bit.ly/SjKCZR
  • If you have a table that contains repeating information in one or more fields, you can use the Table Analyzer to split the data into related tables so that you can store data more efficiently. This process is called normalization. Check out this article to learn more about the Table Analyzer.
http://bit.ly/RYG7qi
  • This article from Microsoft explains how adding tabs to a form can make it more organized and easier to use, especially if the form contains many controls. By placing related controls on separate pages of the tab control, you can reduce clutter and make it easier to work with your data.
http://bit.ly/Tc7Rb5
  • Charts allow you to visually represent data that is presented in your tables or queries. This tutorial from FunctionX provides additional information about creating charts in Access.
http://bit.ly/RSd7gg
  • VIDEO: Access provides PivotTable and PivotChart views to help you summarize and visualize your data. This video shows you how to create pivot views on a new query, add the PivotChart view to a form, and then add that form to a Navigation Form so that the PivotChart can be easily viewed by the users of the database.
  • BONUS: http://bit.ly/VxeQhU Want more info about PivotTables & PivotCharts? This article explains the steps required to create and customize each view.

The presentation above reiterates the key points from the Tutorial. It is great to use while going through the chapter as a notes road-map, however, just reading through the presentation will not be enough to understand the concepts provided in the textbook, you must work through the entire tutorial project.