Databases

Unit 1: Introducing the Database

Lesson 2: Creating a Database Table

 

---

How do you create a new table?

A table is one of the most important objects you will define for your database. A table is a container that will hold the data you store in your database. Tables also define what type of data you can store in your database. A database may have one or more tables. Here is an example of a table from a music collection database:

In the first lesson of this unit we discussed planning. Before you can create a table you must know what fields it will include and the data type of each field.

For this objective let's assume you want to create a database that will keep track of the books you own. After some planning and careful thought about how you will use the database, you decide the fields (and data types) you need are:

• title (text field)
• author (text field)
• price (numeric field, currency to be specific)

The following animation shows how to create a table with the three fields above. The comments following the animation explain the process in more detail.

When you select while the Tables tab is showing you are given five options for creating a new table:

In this course the only two options we will discuss are:

1. Design view
2. Table Wizard

The animation above demonstrates the Design view method of creating a table. With Design view you have the most control. In Design view you specify the name, data type, and properties for each field in your table.

The Table Wizard is much like the Database Wizard we discussed in the first lesson. The Table Wizard lets you create a table from a list of common table definitions. The Database Wizard can be helpful because it may suggest fields you haven't considered.

Regardless of how you initially create your table, you will see in the next objective that you can always return to the Design view and make changes to your table definition.

A field is defined by its:

1. name
2. data type
3. description (optional), and
4. properties

Valid data types are:

Text

Characters and numbers up to 255 characters in length.

Memo

Characters and numbers up to 65,535 characters in length.

Number

Numeric values that may be used in calculations.

Date/Time

Date and time values (good for representing values through the year 9999!).

Currency

Currency fields use a fixed-point calculation method that avoids rounding errors.

AutoNumber

Unique sequential number provided by Access. Good data type for key fields.

Yes/No

Represents a field that can have two values. Very efficient.

OLE Object

Allows you to define a field that contains a complex object, such as a spreadsheet or video clip.

Hyperlink

Reference to a web page or object outside of the database.

Lookup Wizard

Lookup Wizard isn't a data type. The Lookup Wizard allows you to create a field that gets its values from another table or list of values that you provide.

Notice that when we chose to save the table it warned us about not having a primary key field defined. A primary key field is used to create relationships between tables.