Transcript Create tables for a new database

®
®
Microsoft Access 2010 Training
Create tables for a new database
Course contents
• Overview: The essential component
• Lesson: Includes seven instructional sections
• Suggested practice tasks
• Test
• Quick Reference Card
Create tables for a new database
Overview: The essential component
Tables are the essential component of
any database. Without them, you don’t
have a database. In this course, you’ll
learn how to build the tables for a new
database.
If you’re feeling intimidated, relax. We
assume you’re a beginner, and we’ll
show you how to create tables step by
step.
Create tables for a new database
Course goals
1. Create a table in Datasheet view.
2. Set data types for the fields in the table.
3. Create a table in Design view, and set the
primary key and data types for the table.
4. Create a lookup field — a field that provides a
list of choices.
5. Use Design view to change the values in an
existing lookup field.
Create tables for a new database
Create tables
You need to build an
asset tracking database
and move away from a
spreadsheet that’s too
big to use.
You may be familiar with
how to design the tables
for your new database –
the fields, data types,
primary keys, and
foreign keys. Now it’s
time to create the
tables.
Ways to create tables.
Create tables for a new database
Create tables
As a reminder, though,
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. This course
shows you how to use
the most common tools
for building tables:
Datasheet view and
Design view.
Ways to create tables.
Create tables for a new database
Create tables
Here’s the process:
1 In Datasheet view, you
build a table by clicking
a blank field header,
selecting a data type,
and then entering a field
name. All you have to do
is click and type, and
we’ll show you how.
2 For some tables, you can
save time by using Quick
Start Fields, predefined
sets of fields that meet
several common
business needs, such as
capturing addresses or
starting and ending
dates. All you have to do
is select a set of fields
from a menu.
Ways to create tables.
Create tables for a new database
Create tables
Here’s the process:
3 In contrast to Datasheet
view, Design view lets
you control every field
and property in a table.
In this course, you’ll use
it to create a table and to
change the values in a
lookup field — a field
that contains a list of
choices.
Finally, as you go,
remember that if you
want to publish your
database to SharePoint,
you have to use
Datasheet view to create
your tables.
Ways to create tables.
Create tables for a new database
Create a table in Datasheet view
Datasheet view
provides a visual way to
create a table.
1 Start by creating a new,
blank database or by
adding a new table to an
existing database. Either
method opens a new
table in Datasheet view.
2 Notice that the new
table contains a field
called ID. That’s your
primary key, so you
don’t need to create one.
The process, in Datasheet view.
Create tables for a new database
Create a table in Datasheet view
Datasheet view
provides a visual way to
create a table.
3 To add your fields, click
the first blank field
header – the words Click
to Add. That starts a
menu of data types, and
you select a data type
for the field. After that,
the field header then
becomes available for
writing, so...
The process, in Datasheet view.
Create tables for a new database
Create a table in Datasheet view
Datasheet view
provides a visual way to
create a table.
4 You just type the field
name and press ENTER.
Doing that shifts the
focus to the next field,
where you repeat the
process. As you work,
remember that if your
field names contain
more than one word,
don’t use spaces
between the words.
The process, in Datasheet view.
Create tables for a new database
Create a table in Datasheet view
Datasheet view
provides a visual way to
create a table.
5 When you’ve finished,
press CTRL+S, or go to
the Quick Access Toolbar
and click Save. That
starts a Save As dialog
box, where you enter a
name for the table and
then save it.
The process, in Datasheet view.
Create tables for a new database
Go faster with Quick Start fields
Quick Start fields are a
faster way to build parts
of a new table. The
fields capture data for
common business
needs, and all field
names and data types
are set for you.
Adding Quick Start fields to a table.
Create tables for a new database
Go faster with Quick Start fields
1 With a table open in
Datasheet view, click the
Fields tab, and in the
Add & Delete group,
click More Fields. A list
appears.
2 Scroll down the list until
you see the Quick Start
section, click the type of
fields you want to use,
such as Address, or
Name, and...
3 Access adds the fields
for you, with field names
data types already set.
Adding Quick Start fields to a table.
Create tables for a new database
Go faster with Quick Start fields
You can use the new
fields right away — just
start entering data — or
you can rename them,
and remove fields you
don’t need. Also, you
may have noticed what
seem to be spaces in the
field names.
Adding Quick Start fields to a table.
Create tables for a new database
Don’t worry, you’re not
looking at the actual
field names. Instead,
you’re looking at
captions, user-friendly
text associated with
each field name.
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.
You can also open
existing tables in Design
view and add, remove,
or change fields.
Using Design view.
Create tables for a new database
Create a table in Design view
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. And remember
that you don’t need to
add any foreign key
fields now. You can do
that when you create
your relationships.
Using Design view.
Create tables for a new database
Create a table in Design view
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.
As always, save your
changes and give your
new table a name that
describes the data it
contains.
Using Design view.
Create tables for a new database
Add and save data
As you finish your
tables, you’ll probably
enter a few records.
That’s a good way to
test your tables, and to
help make sure you’re
capturing the right data.
Remember a couple of
rules along the way.
The process of saving data.
Create tables for a new database
Add and save data
1 When you enter or
change data, you never
have to click Save to
commit the new
information to your
database. All you have to
do is move the focus to
another record. To do
that in a datasheet, or in
a type of form called a
multiple-items form, you
can click a different row.
You can also use the TAB
or arrow keys to shift the
focus to a new record.
Any of those actions will
commit new data.
The process of saving data.
Create tables for a new database
Add and save data
2 The same is true for
forms. You enter data on
the form, and when you
navigate to a different
record, you commit your
data.
The process of saving data.
Create tables for a new database
Use the record navigation buttons
Once you create your
tables, you’ll need to
know how to use the
record navigation
buttons.
You’ll find them in the
lower-left corner of your
tables, and you’ll also
see them in your query
results, and on most of
your forms.
Access record navigation buttons.
Create tables for a new database
Use the record navigation buttons
You use the buttons to
locate data.
1 Use the First record
button to go to the first
record in a table or
query result.
2 Use the Previous record
button to go to the
previous record.
3 The Current Record box
lists the records in
sequential order, and it
shows you which record
you have selected.
Access record navigation buttons.
Create tables for a new database
4 Use the Next record
button to move to the
next record.
Use the record navigation buttons
You use the buttons to
locate data.
5 Use the Last record
button to move to the
last record, and ...
6 If you need to add data,
click the New (blank)
record button.
Access record navigation buttons.
Create tables for a new database
Add a lookup field to a table
You can sometimes use
a lookup field instead
of a table. For example,
say you need to record
the locations of your
company’s assets.
If you have a large
number of locations,
such as offices on
several floors, you’d
store that data in a table
because it’s easier to
manage. But if you only
have a few, it makes
sense to store those
options in a lookup
field.
Using the Lookup Wizard.
Create tables for a new database
Add a lookup field to a table
A lookup field can store
a list of options
internally, or it can look
up data from a field in
another table.
The following steps
explain how to create a
lookup field that stores
options internally, in
what Access calls a
value list.
Using the Lookup Wizard.
Create tables for a new database
Add a lookup field to a table
1 With your table open in
Datasheet view, click the
Fields tab, and in the
Add & Delete group,
click More Fields.
2 In the menu, click
Lookup & Relationship.
That starts the Lookup
Wizard.
3 On the first page of the
wizard, click I will type
in the values that I
want and click Next.
Using the Lookup Wizard.
Create tables for a new database
Add a lookup field to a table
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.
5 On the third page of the
wizard, enter a name for
the new field and click
Finish.
Using the Lookup Wizard.
Create tables for a new database
Suggestions for practice
1. Create a Suppliers table in Datasheet view.
2. Create a Support table in Design view.
3. Create an Assets table.
4. Create lookup fields in Datasheet view.
5. Create lookup fields in Design view.
Online practice (requires Access 2010)
Create tables for a new 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.
Create tables for a new database
Test question 1
When you create a new table in Datasheet view, you must
define a primary key field.
Answer:
2. 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.
Create tables for a new database
Test question 2
You can’t use the Lookup Wizard to alter an existing value
list. (Pick one answer.)
1. True.
2. False.
Create tables for a new database
Test question 2
You can’t use the Lookup Wizard to alter an existing value
list.
Answer:
1. True.
You use Design view to alter a value list.
Create tables for a new database
Test question 3
When you use Quick Start fields to help create a table,
you must set data types for those fields. (Pick one
answer.)
1. True.
2. False.
Create tables for a new database
Test question 3
When you use Quick Start fields to help create a table,
you must set data types for those fields.
Answer:
2. False.
You can change the data types if you need to, but they’re
set for you.
Create tables for a new database
Test question 4
Which of the following is the correct syntax for a value
list? (Pick one answer.)
1. ’Option 1’,’Option 2’,’Option 3’
2. “Option 1”;”Option 2”;”Option 3”
3. “Option 1”:”Option 2”:”Option 3”
Create tables for a new database
Test question 4
Which of the following is the correct syntax for a value
list?
Answer:
2. “Option 1”;”Option 2”;”Option 3”
Place each option between double quotes and separate
each option with a semicolon.
Create tables for a new database
Quick Reference Card
For a summary of the tasks covered in this course, view the
Quick Reference Card.
Create tables for a new database