The Database Terminology of Access

Before examining the actual table examples in this section, it’s a good idea to have a firm understanding of the terminology that is used when working with databases—especially Access databases. Microsoft Access follows traditional database terminology. The terms database, table, record, field, and value indicate a hierarchy from largest to smallest.

Databases

Generally, the word database is a computer term for a collection of information concerning a certain topic or business application. Databases help you organize this related information in a logical fashion for easy access and retrieval.

Databases aren’t only for computers. There are also manual databases; we simply refer to these as manual filing systems or manual database systems. These filing systems usually consist of people’s names, papers, folders, and filing cabinets—paper is the key to a manual database system. In a real manual database system, you probably have in/out baskets and some type of formal filing method. You access information manually by opening a file cabinet, taking out a file folder, and finding the correct piece of paper. You use paper forms for input, perhaps by using a typewriter. You find information by manually sorting the papers or by copying information from many papers to another piece of paper (or even into an Excel spreadsheet). You may use a spreadsheet or calculator to analyze the data or display it in new and interesting ways.

An Access database is nothing more than an automated version of the filing and retrieval functions of a paper filing system. Access databases store information in a carefully defined structure. Access tables store data in a variety of forms, from simple lines of text (such as name and address) to complex data such as pictures, sounds, or video images. Storing data in a precise, known format enables a database management system (DBMS) like Access to turn data into useful information.

Tables serve as the primary data repository in an Access database. Queries, forms, and reports provide access to the data, enabling a user to add or extract data, and presenting the data in useful ways. Most developers add macros or Visual Basic for Applications (VBA) code to forms and reports to make their applications easier to use.

A relational database management system (RDBMS), such as Access, stores data in related tables. For instance, a table containing employee data (names and addresses) may be related to a table containing payroll data (pay date, pay amount, and check number). Queries allow the user to ask complex questions (such as “What is the sum of all paychecks issued to Jane Doe in 2007?”) from these related tables, with the answers displayed as onscreen forms and printed reports.

In Access, a database is the overall container for the data and associated objects. It is more than the collection of tables, however—a database includes many types of objects, including queries, forms, reports, macros, and code modules.

Access works a single database at a time. As you open Access, a single database is presented for you to use. You may open several copies of Access at the same time and simultaneously work with more than one database.

Many Access databases contain hundreds, or even thousands, of tables, forms, queries, reports, macros, and modules. With a few exceptions, all of the objects in an Access 2007 database reside within a single file with an extension of accdb, .accde, or .adp.

The .adp file format is a special database format used by Access to act as a front end to work with SQL Server data.

Tables

A table is just a container for raw information (called data), similar to a folder in a manual filing system. Each table in an Access database contains information about a single entity, such as a person or product, and the data is organized into rows and columns.

In the section titled “A Five-Step Design Method,” later in this chapter, you learn a successful technique for planning Access tables. In Chapter 36, you learn some of the very important rules governing relational table design and how to incorporate those rules into your Access databases. These rules and guidelines ensure your applications perform with the very best performance while protecting the integrity of the data contained within your tables.

In fact, it is very important that you begin to think of the objects managed by your applications in abstract terms. Because each Access table defines an entity, you must learn to think of the table as the entity. As you design and build Access databases, or even when working with an existing application, you must think of how the tables and other database objects represent the physical entities managed by your database.

After you create a table, you view the table in a spreadsheet-like form, called a datasheet, comprising rows and columns (known as records and fields, respectively—see the following section, “Records and fields”). Figure 33-1 shows the datasheet view of the Contacts table in the Access Auto Auction application.

The Contacts table represents people who work with the Auto Auction. Notice how the table is divided into horizontal (left-to-right) rows, and vertical (top-to-bottom) columns of data. Each row (or record) defines a single contact, while each column (or field) represents one type of information known about a contact entity.

For instance, the top row in tblContacts contains data describing John Jones, including his first name and last name, his address, and the company he works for. Each bit of information describing Mr. Jones is a field (FirstName, LastName, Address, Company, and so on). Fields are combined to form a record, and records are grouped to build the table.

Each field in an Access table includes many properties that specify the type of data contained within the field, and how Access should handle the field’s data. These properties include the name of the field (LastName) and the type of data in the field (Text). A field may include other properties as well. For instance, the Size property tells Access how many characters to allow for a person’s last name. (You learn much more about fields and field properties in Chapter 36.)

Records and fields

As Figure 33-1 shows, the datasheet is divided into rows (called records) and columns (called fields), with the first row (the heading on top of each column) containing the names of the fields in the database. Each row is a single record containing fields that are related to that record. In a manual system, the rows are individual forms (sheets of paper), and the fields are equivalent to the blank areas on a printed form that you fill in.

Values

At the intersection of a row (record) and a column (field) is a value—the actual data element. For example, John, the name in the first record, represents one data value. You may have a couple questions, such as: What makes this row different from other rows in the table? Is it possible to have another John Jones in the same table? If there is more than one John Jones, how does the database tell them apart?