Access 2007: About Relational Databases and Access 2007

Access 2007 is a Relational Database Management System (RDBMS), that is, a database containing relationships allows you to easily find and sort specific information.

Now, if that first sentence sounds foreign, don’t worry. That just means you’re in the right place. This course will start at the very beginning by helping you to understand what a relational database is and how Access 2007 can assist you in your work.

So, let’s start with some basic database terminology:

Access 2007 stores all of your information in Tables. And, a table is a lot like an Excel spreadsheet in that it stores this data in rows and columns. If you use Microsoft Excel, you can think of a table as a “spreadsheet.”

Figure 1. Sample Table

Each table contains Records. These records look like rows and store all of the information related to one set of data, or one person, place or thing. For instance, all of the information about one person (name, address, phone) would be contained in one record. If you use Microsoft Excel, you can think of a record as a “row.”

Figure 2. Sample Record



 

Each table also contains Fields. These fields look like columns and store common sets of information. For instance, the last names of all of the people in your database would be stored in a field. If you use Microsoft Excel, you can think of a field as a “column.”

Figure 3. Field Sample

Finally, one single piece of information is called a Value. If you use Microsoft Excel, you can think of a value as a “cell.”

Figure 4. Value Sample

How Access Stores Data

One of the coolest benefits of a relational database system, like Access, is its ability to quickly sort, filter and find stored information. It’s able to do this by storing data in several small files as opposed to one large, flat file (which, by the way, is the way it was done in the “old” days of database management).

It isn’t uncommon for a database to hold 10, 50 or more tables. When building Access tables, you’ll want to also build to the lowest common denominator. That means you’ll want to keep data related to one entity in an individual table. This may mean that one table may have only two fields.

Let’s look at an example:

Many databases contain information related to people whether they’re employees, customers or vendors. So, let’s look at fields you might need to store information about employees.



Employee Name

Address

State

City

Zip

Phone Number

E-mail Address

Department

Health Plan

Parking Lot

Pay Rate

Hire Date



 



This list isn’t meant to be all-inclusive. I just want to get you thinking about categorizing your data into both tables and fields.

Based on the field list above, you could probably fit everything into one table. But, if you did, you wouldn’t be taking advantage of a relational database management system like Access. Instead, you’ll want to break down these fields into their lowest common denominators.

For instance:



TABLE 1

Employee Name

Address

State

City

Zip

Phone Number

E-mail Address
TABLE 2

Department



TABLE 3

Health Plan

Parking Lot

Pay Rate

Hire Date

 

 

 

 



And, we’re still not done. In addition to breaking down the tables as far as they’ll go, you’ll need to add key fields to each table that allow them to relate to one another.

We’ll get into relationships in more detail in the Access 2007 Intermediate course. What’s important to take away from this lesson are the following key points:

     
  • Access 2007 is a Relational Database Management System.
  •  
  • Data is stored in Tables.
  •  
  • Tables consist of Fields (Columns), Records (Rows) and Values (Cells).
  •  
  • Each table should contain the lowest common denominator by storing information related to one, single subject.