Transcript Microsoft Access Chapter 3

ACCESS 2007
Chapter 3
Microsoft Access 2007 – Level 1
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
MODIFYING AND MANAGING
TABLES
3-1
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
3-2
END
Performance Objectives
• Modify a table design by adding, deleting, or
moving fields
• Assign a default value and validate a field entry
• Insert a Total row
• Use the Input Mask Wizard and the Lookup
Wizard
• Complete a spelling check on data in a table
• Find specific records in a table
• Find specific data in a table and replace with
other data
• Backup a database
• Compact and repair a database
• Use the Help feature
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
3-3
END
Modifying a Table
Modifying a table = changing its structure
Change the table structure to
– Add, delete fields (columns)
– Change data type, description, or length
– Set defaults and validation rules
Click Home tab, View
to switch views
bottom button opens a list of views
Adding, deleting, changing data in records is not
the same as changing the structure of the table
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Adding a Field
In Table Design view
then Click the
Insert Rows button
(in Design tab, Tools group)
Click in row
OR
that will follow
the new field
In Table Datasheet view
Type in column
‘Add New Field’
END
3-4
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Deleting a Field
In Table Design view OR In Datasheet view
Click in row to
be deleted
then
Click in the
column to
be deleted
Click the Delete button
(in Table Tools tab)
Warning message appears … (see next slide)
3-5
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
Deleting a Field…/2
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Read and consider this
message carefully, then click Yes
Cannot UNDO after clicking Yes;
all data in the deleted field(s) will be gone
3-6
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Assigning a Default Value
Table Design view
Click on the desired field, then its properties:
Enter the
Default Value
Default value must match the data type
for the field
3-7
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Validation Rules and Text
Ensures that data being added or edited
in records meets specific conditions
– value entered is compared against
expression (validation rule)
– if the value satisfies the validation rule,
Access accepts the entry
– if the value does not satisfy the validation
rule, Access displays a message
END
3-8
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Validation Rules and Text…/2
Table Design view
Click on field to be validated, then its
properties:
Enter the
Validation Rule
Enter the
Validation Text
If the value entered does not meet the
Validation Rule, Access displays the message
entered in the Validation Text field property
3-9
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Inserting a Total Row
Datasheet view
Click
Totals button
Click in Totals
row in desired
column
END
3-10
Copyright 2007, Paradigm Publishing Inc.
Click desired
function for
that column
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Creating an Input Mask
• An input mask is a pattern for how to
enter data into the field
– data in all records will conform to a
standard format
– Examples: mm/dd/yy
999-99-9999
• The Input Mask Wizard can be used to
create the Input Mask property entry
for a field
• Input mask only works with Text or
Date field types
3-11
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Input Mask Wizard
Controls the data entered in the field
(sets a pattern for how data is entered)
Select desired field
Click in the
Input Mask
property
Click the Build
button (three dots)
END
3-12
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Input Mask Wizard…/2
First dialog box
Click desired
input mask
Click
END
3-13
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Input Mask Wizard…/3
Second dialog box
Try it and
make any desired
changes
Click
END
3-14
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Input Mask Wizard…/4
Third dialog box
Choose how to
store the data
Click
END
3-15
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Using the Input Mask Wizard…/5
Fourth dialog box
Click
Always test the input mask by adding
a few records to the table
3-16
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Creating Lookup Fields
• Confines the data entered to a selection
from a list of values
– looked up from an existing table or query
or a fixed set of values that you define
• Promotes consistency and accuracy
• The Lookup Wizard can be used to
facilitate creating the Lookup field
END
3-17
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Lookup Wizard
Set the field’s Data Type to Lookup Wizard
Click drop-down
arrow in
Data Type column
Click
END
3-18
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Lookup Wizard…/2
First dialog box
Click to indicate
that you want
to type in the
values
Click
END
3-19
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Lookup Wizard…/3
Second dialog box
Click and type
desired values
(press Tab key to
move to next line)
Click
END
3-20
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Using the Lookup Wizard…/4
Third dialog box
Type the
name
for the field
Click
END
3-21
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Using the Lookup Wizard…/5
Try it …
Opens a dialog box to add
or change the list entries
Click the arrow
to display and
select an
available entry
Alternatively, as you start typing the entry,
Access will fill in from the list
3-22
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Modifying Lookup Fields
In Design View, click in the correct field row
to display the Field Properties
Click the
Lookup tab
Can edit
list items
Change to Yes so that entries are
limited to only those in the drop-down list
3-23
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Moving a Field
Table Design view
Click Record Selector
to select entire row
Drag to desired location
END
3-24
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Sorting Records
Datasheet view
Click
END
3-25
Copyright 2007, Paradigm Publishing Inc.
Click in column
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Completing a Spelling Check
Spelling checker …
• looks up words in its dictionary
• offers replacement words for misspelled
ones
• finds duplicate words
• finds irregular capitalizations
In Datasheet view
Click
END
3-26
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Finding and Replacing Data
Datasheet view
Click
Type text to find
END
Set desired search options
3-27
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Finding and Replacing Data…/2
Datasheet view
Click
Replace button
END
Type text to find,
text to replace it
Set desired search options
3-28
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Backing Up a Database File
Office button, Manage, Back Up Database
Opens Save As
dialog box
Makes a copy of the current database
3-29
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Compacting and Repairing
• Compacting rearranges the space used
by the file on the disk
– to use disk space efficiently
– to optimize the performance of the
database
• Compacting the database will
automatically repair it as well
• Be sure that no other users have the
database open before compacting
END
3-30
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
Compacting and Repairing…/2
While the database file is open…
Office button,
Manage,
Compact
and Repair
Database
You may also compact an Access database
that is not open
3-31
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Compacting and Repairing…/3
Set the option to compact and repair a
database file each time it is closed
Office button,
Access Options,
Current
Database
Click
to insert
a checkmark
END
3-32
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
Use Access Help
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Click
or press the F1 key
Type subject
and press
Enter key
OR
Click
a topic
END
3-33
Copyright 2007, Paradigm Publishing Inc.
Can set
where
to
search
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Features Summary
How can you add a field to a table?
In Design view
click in a row
then Click
the
Insert Rows
button
OR
END
3-34
Copyright 2007, Paradigm Publishing Inc.
In Datasheet view
Type under
‘Add New Field’
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Features Summary
How do you delete a field from a table?
In Design view,
Click in row to
be deleted
OR
In Datasheet view
Click in the column
to be deleted
then
Click the Delete button
END
3-35
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Features Summary
How do you save a modified table design?
Quick Access
Save button
OR
Office , Save
END
3-36
Copyright 2007, Paradigm Publishing Inc.
OR Ctrl + S
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Features Summary
How do you back up a database?
Office, Manage, Back Up Database
END
3-37
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
Features Summary
How do you compact and repair a
database?
Office button,
Manage,
Compact
and Repair
Database
END
3-38
Copyright 2007, Paradigm Publishing Inc.
ACCESS 2007
Chapter 3
Coming Next
LINKS TO
OBJECTIVES
• Modify a Table –
Add, Delete, Move
Fields
• Assign a Default
Value
• Validate Data
• Insert a Total Row
• Input Masks
• Lookup Fields
• Sorting Records
• Spelling Check
• Find and Replace
Data
• Backup a Database
• Compact and
Repair a Database
• Use Help
• Features Summary
BACK
NEXT
END
PERFORMING QUERIES
3-39
Copyright 2007, Paradigm Publishing Inc.