Introduction

The title of this book is quite a mouthful but hopefully provides a reasonable idea of the topics to be covered. Data Analysis with Microsoft Access 2010 tells you that the book is about both data analysis and Microsoft Access. The emphasis, however, is on data analysis. There are already many fine books that cover the nuts and bolts of Access from A to Z, but very few discuss Access with the goal of learning what it can accomplish as a data analysis tool. This is such a book.

If you’re wondering what data analysis actually means, you’re not alone. It’s certainly an overused and somewhat trite term, and one that can be interpreted in many different ways. As a result, before we even begin looking at Access, we’ll devote most of Chapter 1 to a general discussion of data analysis and how it relates to Access.

This book covers the latest version of Access. All screenshots are taken from Microsoft Access 2010.

Now for the subtitle: From Simple Queries to Business Intelligence. As you’ll learn in Chapter 2, Access is comprised of five major components: tables, queries, forms, reports, and macros. As indicated by the phrase simple queries, the emphasis in this book is on queries. To a lesser extent, we will also cover tables. Forms, reports, and macros—although useful aspects of Access for the general user—are of limited importance for the data analyst and are therefore covered only marginally in this book.

The book’s subtitle also mentions business intelligence. Coverage of this topic is a unique feature of the book, and one that you won’t find in other Access texts. Like data analysis, business intelligence (BI) is a term that has many different meanings and implications, but as discussed in this book, business intelligence is a natural extension of the analytic process. What many don’t realize is that Microsoft Access offers many of the basic capabilities required for a BI solution. While larger organizations will no doubt choose powerful server-based tools over Access for business intelligence development, it is nevertheless true that Access can be used as a first step for prototyping and development. At the very least, Access can serve as a tool for learning what BI is all about, even if it’s not used in a final solution.

Topics and Features

The topics in this book are many and varied, but in essence, we’ll focus on one main objective:

• How to use Access queries to analyze data

We will also cover numerous related topics, including:

• How to utilize business intelligence concepts in data analysis

• How to create tables and links to data in external data sources

• How to create and utilize pivot tables in both Access and Excel

• Strategies for using Excel in conjunction with Access

We assume no prior knowledge of Microsoft Access. In other words, this is an introductory book on Access, but one that focuses its content on those topics that will be useful for the data analyst.

A number of features make this book unique among introductory Access books:

• The emphasis is on data analysis.

  Access contains many components and can be used in multiple ways in an organization. We focus on its data analysis features, and spend relatively little time on Access’s ability to create self-contained applications for data input and maintenance. Additionally, to handle relatively advanced data analysis issues, we intersperse the text with “Focus on Analysis” sidebars to explore those topics in detail.

• Business intelligence concepts are explored in conjunction with data analysis.

  Business intelligence is both a process and a specific way of organizing data to put it in a more intuitive format for the business analyst. As such, it can prove useful for many data analysis tasks. We’ll show you how to organize your data along those lines.

• You will not be required to sit with a computer as you read the text.

  It will not be necessary to download data or run through exercises as you read the text. The text includes small data samples that allow you to understand how Access queries work simply by reading the book.

• You’ll learn how Access queries relate to SQL.

  SQL is the language that underlies access to data in Access. Many Access analysts may already know something about SQL or have a desire to learn a little SQL as they learn Access. Through the use of special “See the SQL” sidebars, we’ll show you how the Access query concepts you’re learning relate to SQL code. This material is purely optional, so if you have no need or desire to learn SQL, that won’t affect your understanding of anything else.

With this book’s emphasis on data analysis, several topics will receive little or no mention. These topics include:

• Installation

• Security Features

• Database Documenter

• Visual Basic

• SharePoint Links

• Database Repair and Administration

• Pass-Through and Data Definition Queries

In addition, we will not cover Access forms, reports, and macros in the main text. However, we provide an appendix with a brief tutorial on those topics.

Plan of the Book

This book presents its material in a unique sequence. The majority of Access books run through their topics as if the reader were a systems programmer who needs to create and design an Access database from scratch, then create some forms to allow users to enter some data into the database, and then finally start to retrieve that data. In this book, we start with the assumption that the data you want to access and analyze is most often in external databases and spreadsheets. Our motivation is the data itself and the desire to learn more about what it means.

The 16 chapters in the book are broken down into three main parts:

• Part I: Getting Started

  This first section, comprised of two chapters, gets us going with some essential introductory material. The first chapter covers the meaning and purpose of data analysis, and provides a framework for the tools at our disposal. The second chapter presents an overview of Access, with an emphasis on its user interface and various ways to navigate through the software. We also provide some background information on how Access relates to other relational databases.

• Part II: Access Queries

  This second section contains the main material of the book. The chapters in this section can be divided into four main groups:

  Chapters 3 through 5 cover the basics of tables, queries, and joins. This explains how to use tables to import and link to data, and how to design tables with the necessary keys and proper data types. We then get into the various components of the Select query. This provides a way to retrieve data from various sources and is at the heart of the data analysis process. Finally, the chapter on joins discusses ways to retrieve data from multiple tables or queries.

  Chapter 6 steps aside from Access to explore the topic of relational database design. This provides the theoretical framework necessary for our subsequent efforts to organize and analyze data.

  Chapters 7 through 10 delve into various ways to design Select queries to retrieve precisely the data that is needed. These topics include expressions, functions, selection criteria, summarization, and subqueries.

  Chapters 11 and 12 cover a variety of other query types. Chapter 11 explains how Select queries can be converted into Action queries. These queries allow you to update, delete, or insert data rather than simply retrieve it. Chapter 12 covers the important topics of Crosstab queries and pivot tables. These topics lead directly to the third section of this book, the material on business intelligence.

• Part III: Creating Business Intelligence

  The four chapters in this final section explain how to extend data analysis into business intelligence. Chapter 13 provides a theoretical framework for the subject, with a survey of BI software and a discussion of its purpose and features.

  Chapters 14 and 15 move into the details of designing Access queries that can accomplish business intelligence objectives. In these chapters, you’ll learn how to create queries and processes that can model the dimensions and facts of a BI solution.

  Finally, Chapter 16 explains how to utilize the analysis capabilities of Excel pivot tables to explore the business intelligence structure you’ve created. Although Access provides a pivot table view of queries, Excel is usually the better tool for this type of analysis. We’ll also touch on a new Excel tool, PowerPivot, which was introduced with Office 2010.

The two appendices to the book provide supplemental information. Appendix A covers the three main Access topics not discussed in the main part of this book: Forms, Macros, and Reports. With this tutorial, you’ll be up and running with the basics of these other Access features.

Appendix B describes a sample business intelligence database that has been created in Access. This database is available on the book’s companion website.

Companion Website Downloads

You may download companion website files from www.courseptr.com/downloads. Please note that you will be redirected to our Cengage Learning site. Two Access database files are available for download: SalesDatabase.accdb and DataWarehouse.accdb. These files are described in Appendices A and B.

Typographic Conventions

In an effort to keep the book as readable as possible, special typography has been kept to a minimum. Nevertheless, in dealing with software and computer languages, certain conventions must be employed to aid in understanding.

First, we use italics to denote any type of special emphasis. Italics are generally employed when introducing a new term or concept for the first time, to indicate that the word has a special meaning.

We print Access functions, operators, and constants in ALL CAPS. Note that Access itself doesn’t display these items as such. It generally just capitalizes the first one or two letters. For example, we display the function LTrim as LTRIM and the operator And as AND. This helps these words stand out and be recognized as special keywords.

Finally, we apply a number of conventions to the SQL statements that appear in our “See the SQL” sidebars. These are explained when the first sidebar is introduced in Chapter 4.