Database Basics with Microsoft Access 2007

In the previous article in this series, we discussed and defined Microsoft Access 2007 and the differences between Access databases and Excel, Word, and PowerPoint. Access is the database program that is one of the core applications of the Microsoft Office 2007 suite. Access forms the backbone of many dynamic websites and is especially useful when coupled with Active Server Pages (ASP) and ASP.NET scripting languages.

click to enlarge

Data stored in an Access 2007 database can take many forms. Access can store, retrieve, and serve text strings, numbers, calendar dates, images, hyperlinks, and many other types of data. Data need not be confined to just numbers and text. Many people look upon Access as a complicated type of spreadsheet. After all, you may have seen a table from an Access database and noticed that it looks a lot like an Excel worksheet.

There are some similarities between Excel and Access. However, let’s clear up the matter and think of Excel as a two-dimensional database. Excel can store data in rows and columns but that’s about it. Access is a three-dimensional program capable of storing and retrieving beyond just two dimensions like Excel. Read on to learn more about this concept of three dimensional data.

Like Excel, Access 2007 uses fields to store one type of data. Say, for example, you want to store people’s contact information in a database. You would probably find it convenient to create several fields such as “name,” “address,” “city,” and “Phone Number” so that the information in the database was easy to recognize and, later, manipulate or query.

These labels are called fields. Fields can hold many different types of information but you would only want to put phone numbers in the “phone number” field. You would have a lot of trouble finding someone’s phone number if you put it in a field named “address.” The concept of database fields is very similar to the fields you create in Excel. Remember that you can think of Excel as a two-dimensional database so it isn’t surprising that the two programs share some similarities.

Records are a collection of data that contain information in some or all of the fields in your database. For example, the name, address, city, and phone number of an individual in your database is a record. It is a record because all of the information in it is related. In other words, it belongs to the same person.

Access 2007 differs in some regard to other database programs because Access calls a record a “row.” This is because the fields go across the top of a table (a concept we will get to shortly) with each row in the database representing a record. Keep this in mind as you begin building your database. Again, this concept is not unlike Excel. However, Excel does not make use of records or rows as Access does. If you sort a field, say by alphabetical order in Excel, the data to right and left of it does not get sorted with it. Excel does not automatically consider adjacent data to be part of the same record.

Tables are where your fields and data reside within a database. Tables look a lot like Excel worksheets but this similarity is only a superficial one. Humans can easily think in terms of rows and columns so that’s why these programs are laid out that way. Remember, though, that Access 2007 is a three-dimensional database. Displaying data in a two-dimensional form is really for human convenience only.

Tables contain records that have something in common to warrant them residing in the same table. For example, it wouldn’t make much sense to put recipes in your contact information table because recipes and contact information do not share the same fields. Your grandmother’s recipe for chocolate chip cookies does not contain a “name” or “address” any more than your contact information table contains fields for “flour” and “bake time.”

You need to plan a bit and not just start a new database and begin typing in or importing data. You need to know how many tables you need, what fields will be in those tables, and what records you intend to store under the fields.

In addition, an Access 2007 database is more than just the data it contains. It includes the tables, fields, queries, reports, and forms that are stored in the database file. Combined, these elements make an Access 2007 database a powerful tool to store, retrieve, and serve data that no two-dimensional Excel file could ever do. Don’t be fooled by the superficial similarities between Access and Excel. When you start learning the basic and intermediate aspects of Access 2007, you will discover that you are looking at data in a whole new, three-dimensional way.

Access 2007 and Excel share some similarities in that they both use columns and rows to store data. That is about where the similarities end. Excel worksheets are not Access tables and Excel could never produce the queries, reports, and forms that make Access so powerful as a small database solution. Access serves as the backbone of dynamic web pages but also serves as an offline database for storing, retrieving, and serving data. Check out the next article in this series to learn about the two main types of databases you can create with Access 2007.