Microsoft Office Access 2003: Tutorial 2 - Creating and Maintaining A Database

XP

Microsoft Office Access 2003

Tutorial 2 Creating And Maintaining A

Database
New Perspectives on 1
Microsoft Office Access 2003, Second Edition- Tutorial 2
XP
Learn the guidelines for
designing databases
When designing a database, first try to think of all

the fields of data that needs to be stored.
Next, group the fields into tables. Each table will
contain a group of related fields.
You need to select a field in each table to become
the primary key for that table.
When tables will be related to one another, you
need to include a common field in the two tables
that will be used to form the relationship.
XP
Determining database fields
XP
Group the fields into tables
XP
Choose a primary key
Choose a field or fields to serve as the primary key

for the table.
A primary key must uniquely identify each record
in the table.
Primary keys can consist of more than one field.
Primary keys with more than one field are called
composite keys
XP
Include a common field
When one table needs to be related to another

table, you must include a common field.
The common field will be the primary key in one
table.
The common field is referred to as a foreign key in
the related table.
The foreign key in a table can then be used as a
primary key to access the record in the related
table.
XP
Data redundancy wastes space and
can lead to data errors
XP
Setting field properties
When assigning a name to any object in Access, carefully

select a name that will indicate what data is stored there.
Field properties include their data type, field sizes, and an
optional description of the field.
When selecting a field size, make sure the size is big
enough to hold the largest piece of data that will be stored
there.
Do not make the field larger then necessary because this will result
in wasted disk space
Make sure the data type you select for a field is
appropriate for the kind of data to be stored in that field.
XP
Access field types, slide 1
XP
Access field types, slide 2
XP
Create a new database
You can create a new database by using a

Database Wizard or by creating a new, blank
database.
When you create a new database, you will need to
create all the tables, queries, forms, and reports for
the database.
This is the most flexible approach, since you will create
all objects yourself
XP
Create a database without using the
Database Wizard
To create a new, blank database without using the

Database Wizard:
Start Access
Click Blank Database in the task pane
In the dialog box, navigate to the location where you
will save your database
Enter the name of the database in the File name text
box
Click the Create button
XP
Create a new table
XP
Define fields, and specify
a table's primary key
When you create a table, you name the fields and define
the properties for the fields.
The table structure is set up and modified in Design View
In Design View, you will define each field that will be included in
the table
When all the fields have been defined, you will then
specify which field(s) will be the primary key.
The primary key is not mandatory, but it is a good idea to assign
one
XP
The database Design View window
XP
Choosing a primary key
Click a row selector for the field you want

to use as the primary key, and then click the
Primary Key button on the toolbar.
After clicking the Primary Key

button, a key symbol appears in the
row selector to indicate the key field.
XP
Save the new table
When all fields have been defined and properties

have been set, you must save the table structure:
Click the Save button on the Table Design toolbar
Type the name you want to assign to the table into the
Table Name text box of the Save As dialog box
Click the OK button to save the table using the
specified name
XP
Add records to a table
using Datasheet View
Once the table has been created, you can then add records to the table
using Datasheet View.
As you enter records, they will be placed in the order in which you
enter them. However, when you close the table and open it again, the
records will be ordered according to the primary key order.
The navigation bar at the bottom of the Datasheet view will indicate
how many records are in the table and what the current record is (i.e.,
the record on which your cursor currently rests).
You can move through the fields in the table by pressing the Tab key.
Once your cursor is moved off a record, that record is automatically
saved on your disk.
XP
Datasheet View with 2 records added
Modify the structure of a table byXP
deleting, moving, and adding fields
The structure of a table can be modified after it has been

created.
To delete a field, enter Design View, right-click on the
field, and then click Delete Rows on the drop down menu.
To move a field, (you also do this in Design View) click
on the field you want to move, and while holding your
mouse button down, move the field to the desired location.
To add a field in Design View, right-click the field where
you want to insert the new field and then click Insert Rows
on the dropdown menu.
XP
Moving a field in Design View
XP
Adding a new field in Design View
1. Right-click the row selector for the 2. Select Insert Rows from the
field you want to insert the new field shortcut menu, and a new, blank
in front of--StartDate in this figure. row will be inserted before the
row you selected, as shown below.
3. Enter the
information for
the new field.
XP
Change field properties
You can make additional modifications to the structure of

a table by changing the properties of the table's fields.
For example, if you have a field that represents a currency value
but you do not want to display dollar signs, you could change the
format to a standard format
In the Design View, you will find a list of all formats available to
you. You can make a selection from the list of options
Each data type has a separate set of options available in the Field
Properties portion of the Table Design Window
Field properties are changed in the Design View window.
XP
Changing field properties
in Design View
XP
Copy records from another
Access database
If the data you want in your database already exists in

another Access database, you can copy the records from
that database into your database.
You can use cut and paste to do this:
Open the database that contains the records you want to copy
Select the records you want to copy and then press the Copy
button. This places the records onto the Windows clipboard
Open the database to which you want to copy the records and open
the desired table to receive the copied records
Place your cursor on the next available row in the table and press
the Paste button
XP
Import a table from
another Access database
You can also import an entire table, including its

structure, into another database.
To import a database table:
Click the File menu
Point to Get External Data, and then Import
Locate and select the database and the table you want
to import
This differs from the cut and paste method
because you actually import the entire table, its
structure, and its data.
XP
The Import Objects dialog box
XP
Copy and import considerations
If you choose to copy records from one table to

another, it is very important that you first
determine that the two tables have the exact same
data structure.
If the two data structures differ in any way, the copy
will cause an error
When you import, you are importing an entire
table, including its data and its structure.
This is a good way to include a table in your database
that was previously designed in a different database
XP
Delete and change records
Once records have been added to a table, they can be

deleted or modified:
In Datasheet view, click the row selector for the record(s) you want to
delete and then click the Delete Record button on the toolbar
To change or modify a record:
Place your cursor on the record and then on the field you want to change
You can then edit the field's value as you wish by typing in new data
You can switch from navigation mode to editing mode
depending on what you want to do at the time.
When you are editing a record, you are in editing mode, Otherwise you
are in navigation mode
Editing mode is indicated by a pencil symbol in the left margin of the
datasheet view
XP
An alternate method
for deleting a record
XP
Keystroke techniques for
navigation and editing modes

Microsoft Office Access 2003: Tutorial 2 - Creating and Maintaining A Database

Uploaded by

Copyright:

Available Formats

Microsoft Office Access 2003: Tutorial 2 - Creating and Maintaining A Database

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Microsoft Office Access 2003: Tutorial 2 - Creating and Maintaining A Database

Uploaded by

Copyright:

Available Formats

XP

Microsoft Office Access 2003

Tutorial 2 Creating And Maintaining A

When designing a database, first try to think of all

Choose a field or fields to serve as the primary key

When one table needs to be related to another

When assigning a name to any object in Access, carefully

You can create a new database by using a

To create a new, blank database without using the

Click a row selector for the field you want

After clicking the Primary Key

When all fields have been defined and properties

The structure of a table can be modified after it has been

You can make additional modifications to the structure of

If the data you want in your database already exists in

You can also import an entire table, including its

If you choose to copy records from one table to

Once records have been added to a table, they can be

You might also like