lesson, you learn how to create a table by using the Table Wizard.
As discussed in Lesson 1, your
tables really provide the essential framework for your database.
Tables not only hold the data that you enter into the database, but
they are designed so that relationships can be established between
the various tables in the database. Tables can be created from
scratch, as discussed in the next lesson, or they can be created
using the Table Wizard.
The Table Wizard can save you a lot
of time by supplying you with all the needed fields and field
formats for entering your database information. Access provides a
large number of different kinds of tables that you can create with
the wizard. The wizard is also fairly flexible, allowing you to
select the fields the table will contain and the way in which they
will be arranged. You can also change the name of a field during the
process. If the wizard doesn't provide a particular field, you can
always add it to the table later, as discussed in Lesson 5, "Editing
a Table's Structure."
To create a table using the Table
Wizard, make sure you've opened a database as described in Lesson 2
and then follow these steps:
In the database window, click the
object icon, and then double-click the
Table by Using Wizard
icon. The Table Wizard opens.
On the first Table Wizard screen, you can select from two
categories of table types:
Your choice determines the list of sample tables that appears (see
Figure 3.1. Select either the
Business or Personal category to view a list of tables.
Select a table in the Sample Tables list; its fields appear
in the Sample Fields list.
To include a field from the Sample Fields list in the table,
select the field and click the
button to move it to the
Fields in My New Table
list. You can include all the fields in the Sample Fields list by
If you want to rename a field that you have added, click the
Rename Field button,
type a new name into the Rename Field box, and then click
Repeat steps 3 and 4 as needed to select more fields for the
table. You can select fields from more than one of the sample tables
for the table that you are creating (remember that you want fields
in the table related only to a particular theme, such as customer
information). When you're finished adding fields, click
The next screen asks you to provide a name for the table (see
Figure 3.2). Type a more descriptive name if necessary to replace
the default name.
Figure 3.2. Provide a name for the
table and allow the wizard to select a primary key for the table.
This dialog box also asks whether you want the wizard to
create a primary key for the table or allow you to select the
primary key yourself. For example, CustomerID is an excellent
primary key because each customer is assigned a different ID number.
In this case, click
Yes, Set a Primary Key for Me
to have the wizard choose your primary key field. You can learn to
set your own primary keys in Lesson 4, "Creating a Table from
The field that uniquely identifies each record in the table.
Every table must have a primary key. This is usually an ID
number because most other fields could conceivably hold the
same data for more than one record (for example, you might
have several people with the last name of Smith).
to continue. Because you're allowing the wizard to select the
primary key, you are taken to the last wizard screen. On the last
wizard screen, you have the options of modifying the table's design,
entering data directly into the new table, or having the wizard
create a data entry form for you. To see the table the wizard
created, go with the default: Enter Data Directly into the Table
(see Figure 3.3).
Figure 3.3. After completing the
table, you can have the wizard open it so that you can enter data.
The new table appears in the Access
workspace (see Figure 3.4). From here you can enter data into the
table, the specifics of which are discussed in Lesson 6, "Entering
Data into a Table." When you close the table, it appears in the
Object pane of the database window (you must also select the Tables
Figure 3.4. Your new table appears
in the Access workspace when you close the Table Wizard.
lesson, you learn how to create a table in Table Design view.
Although the Table Wizard provides
an easy method for quickly creating tables, it does not provide you
with complete control over all the aspects of creating the table's
structure. It does allow you to select the fields used in the table
from a set list, but it restricts you to only those predefined
fields (there are also several types of fields, each used for a
different data type).
When you work with tables you work
in two different views: the Datasheet view and the Design view. The
Datasheet view is used to enter, view and edit data. The Design view
is available to create and edit a table's structure. Creating tables
from scratch in the Design view allows you to build the table from
the bottom up and gives you complete control over all aspects of the
This view allows you to enter field names, select the data
type that a field will hold, and customize each field's
properties. A Design view is available for all the Access
objects, including tables, forms, queries, and reports.
The Design view isn't the only way
to create a table from scratch in Access. You can also create a
table in the Datasheet view by labeling your field columns directly
on the table's datasheet; this method is similar to creating a
worksheet in Excel. We will take a look at both methods for creating
a new table.
This view places each record in a separate row and each
field in a separate column (column headings are provided by
the field names). This view is used to enter data directly
into the table. You will use the Datasheet view whenever you
want to view the records in the table or add or edit
When you create a table in the
Design view, you are creating the structure for the table; you
create a list of the fields that will be in the table. You also
select the data type for each field. (Fields can hold text, numbers,
even graphics—you learn the types of fields that can be created
later in this lesson.) You also have the option of entering a
description for each field. Field descriptions are useful in that
they provide a quick summary of the type of data that goes into the
Another issue that relates to
creating a table in the Design view (or editing a table's structure
in the Design view) is that any changes you make must be saved
before closing the table. If you have worked in other applications,
such as Word or Excel, you might think that saving your work is just
common sense. However, when you actually start working on entering
data into a table or a form, Access automatically saves your records
as you enter them. Therefore, in Access, you need to remember to
save only the changes that you make to the structure of a table,
form, query, or report. You learn more about this in Lesson 5,
"Editing a Table's Structure."
To create a table in Table Design
view, follow these steps:
In the database window (of any database) click the
icon if necessary, and then double-click
Table in Design View.
The Table Design view opens (see Figure 4.1).
Figure 4.1. The Table Design view
allows you to create the structure for your table.
Be sure that the insertion point is in the first row of the
Field Name column. Type the field name for the first field in your
table. Then, press
to move to the Data Type column.
When you move to the Data Type column, an arrow appears for a
drop-down list. The default data type setting is Text; several other
data types are available, such as AutoNumber, which automatically
numbers each of your records (incrementally). This field type is
excellent for customer number fields or employee ID fields. Click
drop-down list and select a field type that suits your needs. The
different data types are discussed later in this lesson, in the
section "Understanding Data Types and Formats."
After selecting the data type, press
to move to the Description column; type a description for the field.
(This is optional—the table will work fine without it—however,
describing the fields reminds any user of the database what type of
information should go into that particular field.)
Enter other fields and their field types (descriptions are
optional) as needed. Figure 4.2 shows the structure for a table that
will be used to enter product information.
Figure 4.2. A table's structure
consists of several fields; fields may differ by field type.
Setting the Primary Key
An important aspect of table
structure design is that each table must have a field that is used
to uniquely identify the records in the table. This field is called
Setting an appropriate key is trickier than it seems because no two
records can have the same key value. In a table of customers, for
example, you might think the Last Name field would be a good key,
but this theory falls flat as soon as you have more than one
customer with the same last name. A more appropriate primary key for
your customers is a Social Security number (although people don't
like to give these out) because it uniquely identifies each
A good general rule is to create an
identification field, such as a customer number, that allows you to
assign a sequential number to each customer as you add them to your
database table. Access can even help you out with the assigning of
numbers to the customers because you can make the field type for the
Customer Number field AutoNumber. An AutoNumber field type assigns a
number to each record starting with the number 1.
To set a primary key, follow these
In Table Design view, select the field that you want for the
Key, or click the
Primary Key button on
the toolbar. A key symbol appears to the left of the field name, as
shown in Figure 4.3.
Figure 4.3. The primary key field is
marked by a key symbol.
you select the primary key and have finished entering your table
fields, you should save the table. Click the
button on the Table Design toolbar to open the Save As dialog box.
Enter a name for the table, and then click
After saving the table, you can either
switch to the Datasheet view (to enter data) by clicking the
button on the toolbar, or you can choose to close the table by
clicking the table's
Understanding Data Types and Formats
To assign appropriate data types to
the fields you create in a table, it is necessary for you to know
what differentiates the different data types available for use with
your table fields. When you create a field, you want to assign it a
data type so that Access knows how to handle its contents. The
following are the different data types you can choose:
Text and numbers up to 255 characters (numbers that are not going to
be used in calculations).
Numbers used in mathematical calculations.
Date and time values.
Numbers formatted for currency.
Sequentially numbers each new record. Only one AutoNumber field can
be placed in a table. This field type is typically used for the
primary key field.
Lets you set up fields with a true/false data type.
OLE (Object Linking and Embedding)—
A picture, spreadsheet, or other item from another software program.
A link to another file or a location on a Web page. This field type
lets you jump from the current field to information in another file.
This field type chooses its values from another table.
In addition to a field type, each
field has other formatting options you can set. They appear in the
bottom half of the dialog box, in the Field Properties area. The
formatting options change depending on the field type; there are too
many to list here, but Table 4.1 shows some of the most important
ones you'll encounter.
Table 4.1. Formatting
Options for Data Types
maximum number of characters a user can input in that field
(applies only to text fields).
drop-down list of the available formats for that field type.
You can also create custom formats.
number fields, you can set the default number of decimal
places that a number shows.
field is usually going to contain a certain value (for
example, a certain ZIP code for almost everyone), you can
set that as the Default Value option. It always appears in a
new record, but you can type over it in the rare instances
when it doesn't apply.
if a particular field is required to be filled in each
The best general rule for setting
the data type for the field is to take a moment to consider what
kind of data will go into that field. For example, if you are
working with the monetary value of a product, you will probably want
to use currency.
The different formatting options
provided for a field in the Field Properties box are often used to
help make sure that data is entered correctly. For example, the
Field Size option can be used to limit a Number data type field to
only a single or double digit. In the case of the default value, you
can actually save data entry time because you use this option when a
particular field almost always has a certain value or text entry.
After you feel comfortable creating
new tables in the Design view, you might want to dive right in and
create tables in the Datasheet view. Creating tables this way
immediately creates a table with 20 field columns and 30 record
rows. This method still requires, however, that you enter the Table
Design view to specify the key field, the field data types, field
descriptions, and any field property changes.
Creating tables in the Datasheet
view is really useful only if you feel the need to quickly enter
some data into the table before setting up the table's properties.
To create a table in the Datasheet view, follow these steps:
In the database window (with the Table icon selected),
Create Table by Entering Data.
A new table in Datasheet view appears in the Access workspace (see
Figure 4.4. Tables can be created in
the Datasheet view.
To enter the field names, double-click any field column
heading (Field1, Field2, and so on). Then, type in the field name.
After you have placed the field names, you can begin entering
Creating a table in the Datasheet
view might be fine for quickly entering data, but you will still
probably need to switch to the Table Design view at some point and
set up the various field data types and properties.
You can switch to the Design view from the
Datasheet view by clicking the
icon on the Table Datasheet toolbar. Remember to save any changes to
the table's design that you make in the Design view.