Access XP
Database Tables
Introduction
By the end of this lesson, you should be able to:
- Understand datasheet basics
- Understand field properties
- Understand table relationships
- Understand the role of the primary key
Datasheet basics
The core component of a database is a table . Data is defined and stored in a table. Multiple tables—each consisting of different types of data—can be created in a database.
Each row in the database is called a record . The entry for John Smith is called a record. The entry for Martha Tompkins is also a record. Each row or record is made up of columns or fields —L.Name, F.Name, Phone, Address, City, State, and Zip—which contain a particular piece of information.
| L.Name | F.Name | Phone | Address | City | State | Zip |
| Smith | John | 919.555.6320 | 100 Paramount Parkway | Morrisville | NC | 27560 |
| Tompkins | Martha | 919.555.6427 | 97 Hummingbird Court | Cary | NC | 27513 |
In a contact management database, a list of names—contacts to whom you have sent resumes or have met through your personal network—might be maintained in a table, along with their addresses, phone numbers, and other personal information.
Field properties
Each table contains a number of columns called fields or data types . Fields are unique pieces of information that make up the information in a table. Tables usually contain multiple fields.
In a previous example, we mentioned that a table might consist of the fields Last Name , First Name , Phone, Address, City, State, and Zip. Each field has unique properties. Some contain characters, while others contain numbers. These field properties are defined when the table is created.
Understanding table relationships
Databases can be simple—consisting of a single table—or made up of many different tables. If you were to convert your resume into a database, for example, you might have a table that contains your name and personal mailing address. We might call this the Contact_Information table .
Your work experience is a different type of information. Instead of identifying who you are or where you live, it identifies the companies you worked for, their addresses, your job title, and your responsibilities. Because this set of information is independent from your contact information, we might instead create a second table called the Work_Experience table .
The same is true of your educational background. It has no direct bearing on your contact information or the companies where you worked. A third table might be created called the Education table to save this kind of data.
The database contains three tables, each independent of the other and all containing different types of information. The database needs a way to connect these three tables.
Primary keys
Every table in Microsoft Access must have at least one field that uniquely identifies each record in the table. This field is known as a primary key. This primary key essentially opens the door to the table and allows you to retrieve information from the table.
The primary key is the mechanism by which you relate different tables and combine information for viewing (query) or printing (report) .
Challenge!
Let's assume that a database table called Contacts contains all of the fields listed in the first column of the chart below. The chart also contains four other columns:
- Text, numbers, or both : Should the field accept data entry input in the form of text, numbers, or both?
- Field size : This is the number of spaces you think the field should have.
- Required entry : Should the field be required entry (the record cannot be added to the database without the information) or optional entry (a record can be added without entry in the field)?
- Allow duplicates : Should the database allow duplicate entries for the field (e.g., can several people in the database share the same zip code)?
Practice designing fields by filling out the following table :
| Text, numbers, or both? | Field size | Required entry? | Allow duplicates? | |
| First Name | ||||
| Last Name | ||||
| Company | ||||
| Address | ||||
| City | ||||
| State | ||||
| Zip Code | ||||
| Country | ||||
| Contact ID | ||||
| Title | ||||
| Work Phone | ||||
| Work Extension | ||||
| Mobile Phone | ||||
| Fax Number |