Transcript Create tables for a new Access database

ICT Staff Development presents:
®
Microsoft Office
®
Access 2007 Training
Build a database II:
Create tables for a new
Access database
Course contents
• Overview: Build tables, and the data will come
• Lesson: Create your tables
The lesson includes a list of suggested tasks and a set of
test questions.
Build a database II: Create tables for
a new Access database
Overview: Build tables, and the data bill come
You’re creating a database because
the spreadsheet you’ve been using is
too big to manage. This course
explains how to build the tables for
your new database.
Why tables? They’re the central
component of your database. Without
them, you can’t store data.
If you’re feeling intimidated, relax. We
assume you’re a beginner.
Build a database II: Create tables for
a new Access database
Course goals
• Create a table in Datasheet view.
• Set data types for the fields in the table.
• Create a table by using a table template.
• Create a table in Design view, and set the primary key for
the table.
• Create a lookup field — a field that provides a list of
choices.
• Use Design view to change the values in an existing
lookup field.
Build a database II: Create tables for
a new Access database
Lesson
Create your tables
Create your tables
In a relational
database, tables store
your data.
Your data doesn’t “live” anywhere else, and that makes
tables the central component of your database.
If you don’t have tables, you don’t have a database.
Build a database II: Create tables for
a new Access database
Create your tables
Access gives you
several ways to create
tables, and this course
shows you how to use
three of them.
They are Datasheet
view, table templates,
and Design view.
1
In Datasheet view, you build a table by entering field
names and setting data types manually. All you have to
do is click and type.
2
Table templates are pre-made tables that meet several
common business needs.
Build a database II: Create tables for
a new Access database
Create your tables
Access gives you
several ways to create
tables, and this course
shows you how to use
three of them.
They are Datasheet
view, table templates,
and Design view.
3
Design view lets you control every field and property in
the table. In this course, you’ll learn how to create a
table and to change the values in a lookup field — a field
that contains a list of choices.
Build a database II: Create tables for
a new Access database
Create a table in Datasheet view
Datasheet view
provides a visual way
to create a table.
The picture shows the
process.
1
You start by creating a new, blank database or by adding
a new table to an existing database.
2
The new table opens automatically in Datasheet view.
Notice how the new table contains a field called ID.
That’s your primary key, so you don’t need to create one.
Build a database II: Create tables for
a new Access database
Create a table in Datasheet view
Datasheet view
provides a visual way
to create a table.
The picture shows the
process.
3
To add your fields, double-click the first field header and
enter the name for the field. When you finish, press ENTER.
4
Go to the next blank field and enter a name in that
header. Repeat the process until you’ve entered names
for all your fields, but with a couple exceptions: don’t add
any lookup fields, and don’t add foreign key fields.
Build a database II: Create tables for
a new Access database
Create a table in Datasheet view
Datasheet view
provides a visual way
to create a table.
The picture shows the
process.
5
When you’ve finished, go to the Quick Access Toolbar
and click Save. In the Save As dialog box, enter a name
for the table and click OK.
Build a database II: Create tables for
a new Access database
Set data types
After you enter field
names, you’ll need to
set the data type for
each field.
Data types control
what you can enter
into a field.
1
Select the field that needs a data type. Just click the field
header.
2
If it isn’t already selected, click the Datasheet tab.
3
In the Data Type & Formatting group, open the Data
Type list and select an option.
Build a database II: Create tables for
a new Access database
Set data types
After you enter field
names, you’ll need to
set the data type for
each field.
Data types control
what you can enter
into a field.
If you’re unsure about which data type to choose, look at
your field names.
If you’re unsure about which type to select, stay with the
Text data type because it’s the most flexible.
Build a database II: Create tables for
a new Access database
Create a table using a table template
Table templates are a
fast way to build a
new table. The
templates capture
data for common
business needs, and
all the fields and data
types are set up for
you.
1
Click the Create tab.
2
In the Tables group, click Table Templates, and then
click the template you want to use.
Build a database II: Create tables for
a new Access database
Create a table in Design view
Design view allows
you to build a table
from scratch and set
or change every
available property for
each field.
1
On the Create tab, in the Tables group, click Table
Design.
2
In the Field Name column of the designer, enter the
names of your table fields. As a rule, the first field you
create should be your primary key field.
Build a database II: Create tables for
a new Access database
Create a table in Design view
Design view allows
you to build a table
from scratch and set
or change every
available property for
each field.
3
In the Data Type column, use the list next to a field
name to choose a data type for that field.
4
Optionally, use the Field Properties pane to set
properties for individual fields.
Build a database II: Create tables for
a new Access database
Add a lookup field to a table
The first course in this
series mentioned that
you can sometimes
use a lookup field
instead of a table.
Lookup fields can store a list of options internally, or they
can look up data from fields in other tables.
Build a database II: Create tables for
a new Access database
Add a lookup field to a table
These steps explain
how to create a lookup
field that stores
options internally, in
what Access calls a
value list.
1
Switch your table to Datasheet view. You can do that by
right-clicking the tab for the table and clicking Datasheet
view.
2
Click the Datasheet tab, and in the Fields & Columns
group, click Lookup Column to start the Lookup Wizard.
Build a database II: Create tables for
a new Access database
Add a lookup field to a table
These steps explain
how to create a lookup
field that stores
options internally, in
what Access calls a
value list.
3
On the first page of the wizard, click I will type in the
values that I want and click Next.
4
On the next page of the wizard, make sure the Number
of columns box contains a 1 and then enter your
options in the grid, one option per row.
Build a database II: Create tables for
a new Access database
Add a lookup field to a table
These steps explain
how to create a lookup
field that stores
options internally, in
what Access calls a
value list.
5
On the third page of the wizard, enter a name for the
new field and click Finish.
Build a database II: Create tables for
a new Access database
Suggestions for practice
1. Create a table in Datasheet view.
2. Create a table using a template.
3. Create a table using Design view.
4. Create and then alter a lookup field in Datasheet view.
Online practice (requires Access 2007)
Build a database II: Create tables for
a new Access database
Test question 1
When you create a new table in Datasheet view, you must
define a primary key field. (Pick one answer.)
1. True.
2. False.
Build a database II: Create tables for
a new Access database
Test question 1: Answer
False.
The “ID” field in the new table acts as the primary key. You can change
the field name, or replace the field with another primary key, but a new
datasheet always contains a primary key.
Build a database II: Create tables for
a new Access database
Test question 2
You can’t use the Lookup Wizard to alter an existing value
list. (Pick one answer.)
1. True.
2. False.
Build a database II: Create tables for
a new Access database
Test question 2: Answer
True.
You use Design view to alter a value list.
Build a database II: Create tables for
a new Access database
Test question 3
When you use a template to create a table, you must set data
types for the fields in the new table. (Pick one answer.)
1. True.
2. False.
Build a database II: Create tables for
a new Access database
Test question 3: Answer
False.
You can change the data types if you need to, but they’re set for you.
Build a database II: Create tables for
a new Access database
Quick Reference Card
For a summary of the tasks covered in this course, view the
Quick Reference Card.
Build a database II: Create tables for
a new Access database