Chapter 5: Analyzing Data with Microsoft Access

Overview

As you’ve discovered over the last couple of chapters, Microsoft Excel is a great software application for analyzing data, especially lists of nonrelational data that comprise a few data rows or up to tens of thousands of data rows. Microsoft Access, however, is better able to handle the creation, storage, and analysis of relational data and is better suited to situations in which data is entered by multiple users at the same time and for large data sources that are several hundred megabytes or larger in size.

Access includes tools and features for sorting, filtering, and querying data. Access also includes a report writer that you can use to present visually compelling reports that group data and calculate totals and trends. In Access 2002, PivotTable views and PivotChart views allow you to bring the powerful data analysis features of PivotTable reports and PivotChart reports to the work you do in Access. Also, Access easily integrates with data stored in Microsoft SQL Server 2000 and provides tools for accessing data over the Web.

Objectives

In this chapter, you will learn

• The main differences between relational and nonrelational data.

• How to import data into an Access database and how to link tables in an Access database to tables in an external data source.

• How to retrieve the data you need to analyze (to filter, sort, and query data) from Access tables and forms.

• How to create Access reports organized with data grouping and subtotals.

• How to analyze data by using Access PivotTable views and PivotChart views.

• How to use Access to work directly with SQL Server 2000 databases.

• How to create Access data pages to view data over the Web.