Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Before you start analyzing data with Access, you need to understand the differences between nonrelational data and relational data.

Nonrelational data is often a list of row-and-column data records. The data is self-contained and does not rely on other data sources or data tables to convey all the facts about itself. An example of nonrelational data is the information in the rows in an Excel worksheet. Usually most, if not all, of the facts about the data in an Excel worksheet are included on the worksheet itself. See Figure 5-1 for an example of nonrelational data.

Figure 5-1: Sample nonrelational data.

In this simple group of records, all you need to know is contained within the records themselves. However, the customer data is repeated, which results in a larger file size, an increased risk of data entry errors, and time wasted updating identical field values if customers change their address information.

Relational data, on the other hand, relies on other data sources or data tables to convey all the facts about itself. When you’re working with relational data, you usually consult more than one related table to assemble a complete set of facts. Figure 5-2 shows an example of a single data table that would draw on other data tables to provide the set of facts shown in Figure 5-1.

Figure 5-2: Single data table that relies on other relational data tables.

In this example, you see the data records for customer purchases, but you don’t see the customer’s name, address, and so on. To do so, you would refer to a data table (or tables) that provides this information. The benefits of relational data can include a smaller file size (in this example, no customer address information is repeated), and if a customer changes its address information, the data needs to be updated in only one record in one data table, reducing data entry errors and data input time.

To relate data tables to one another, you use primary keys and foreign keys. A primary key is a unique value that identifies one and only one data record in a table. For example, you can use personal identification numbers, United States social security numbers, credit card numbers, telephone numbers with country or region codes and area codes, or any other unique series of characters for a primary key. A foreign key is a field in a data table in which each value in that field matches a primary key value in another data table. For instance, if you use the characters ALF001 in a primary key field to identify a customer in a table of customer billing addresses, you could use ALF001 in the foreign key field in a table of sales orders to refer to customer ALF001, thereby relating the customer billing address to the customer’s orders. Because customer ALF001’s address is stored only once, if you need to update ALF001’s billing address, you need to update it only in the billing address table and not for every record for customer ALF001 in the sales order table.

Understanding how relational data is stored is important because to gather a complete set of facts in relational data sources, you often need to cross-reference two or more data tables. Returning a complete set of facts requires referencing multiple primary and foreign keys to get at the correct records in multiple data tables.

For more information about building relational databases in Access, you can read one or more of the books listed on the next page.

• Designing Relational Database Systems by Rebecca Riordan (Microsoft Press, 1999).

• Microsoft Access Version 2002 Inside Out by Helen Feddema (Microsoft Press, 2002).

• Microsoft Access Version 2002 Step by Step by Online Training Solutions, Inc. (Microsoft Press, 2001).

• Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez (Addison-Wesley, 1996).