Access 2003 Page 1 of 13
Microsoft Office 2003
Concepts and Techniques
MAINTAINING A DATABASE USING
THE D ESIGN AND UPDATE F EATURES OF ACCESS
Students will have mastered the material in this project when they can:
Add, change, and delete records Specify validation rules, default values,
Search for records and formats
Filter records Create and use a Lookup field
Update a table design Specify referential integrity
Format a datasheet Use a subdatasheet
Use queries to update records Sort records
This project covers the issues involved in maintaining a database. Students learn how to use
Form view to add a record. They learn how to locate and filter records. Students change and
delete records. They also learn how to change the structure of a table, create validation rules, and
specify referential integrity by creating relationships. Students learn how to use update, delete,
and append queries. They learn how to create indexes to improve performance. F inally, students
learn how to use the backup and recovery features of Access.
Case Perspective, AC 113
The Case Perspective presents a real- life situation in which Microsoft Access could be used and
offers background for the database maintained in this project (Figure 1-1). Review the Case
Perspective. Note how Access will be used to maintain the database.
Introduction, AC 114
Explain the need for maintaining the database. Define restructure the database. Emphasize
the importance of keeping databases up-to-date.
Page 2 of 13 Project 3: Maintaining a Database Using the Design
and Update Features of Access
Ask students what type of maintenance a student database requires (entering grades, changing
addresses, and so on).
If a database is not maintained or if incorrect data is entered into the database, serious problems
can occur. What problems could occur if a student database is not maintained? What problems
could occur if a database that maintains financial data (such as a credit card database) has
What are six types of activities than could be involved in maintaining a database? (Answer:
Changing, deleting, adding records; changing, deleting, adding fields, creating indexes.)
Project Three – Maintaining the Ashton James College database, AC 115
Use Figure 3-1 to review the types of tasks that students will do in this project. Relate these tasks
to the Case Perspective for Ashton James College.
Opening the database, AC 115
Review Steps 1 through 6 on page 116 to open a database. Discuss More About The Access Help
System on page 116.
Before students begin the activities in this project, it is a good idea for them to make a copy of
the database. Review the procedures for copying files. In many network environments, copying
files is really a two-step process. Students first must copy the database file to a temporary
network disk and then copy the file to another disk in drive A. This often is confusing, especially
to students in introductory computer courses. Students should never try to make a backup by
doing a Save As in Microsoft Access and replacing the disk in drive A with another disk. This
will corrupt the database so that it cannot be repaired. Page AC 162 at this end of this project
covers special database operations such as backing up and recovering database files.
Updating records, AC 116
Mention that keeping a database up-to-date requires all three tasks – adding records, changing
records, and deleting records.
Adding records, AC 116
Review the differences between Datasheet view and Form vie w. Use Figures 3-2 through 3-4 to
explain using a form to add records. In both Datasheet view and Form view, records are ordered
by primary key. To use the keyboard to insert a record, press CTRL+PLUS SIGN (+). Discuss Other
Ways to add a record.
Access 2003 Page 3 of 13
Searching for a record, AC 117
Define searching. Consider the value of the Find option. Point out that often the specific record
number of the record of interest is not known, so an option like this is essential. Note that the
field for the search is selected before clicking the Find button. By selecting a field, values are
being restricted to that field. Wildcards also can be used. For example, clicking the Name field,
and entering Fa* in the Find What text box will find all records where the client name begins
with Fa. Use Figures 3-5 and 3-6 to explain searching for a record. Discuss Other Ways to search
for a record. Discuss the Q&A on page AC 119.
What icon appears on the Find button on the Form view toolbar? (Answer: A pair of field
Changing the contents of a record, AC 119
Differentiate between Insert mode and Overtype mode. Make sure students understand that
there are several ways to make changes to a record. Pressing the ESC key will undo changes to a
current record. To replace an existing value with that of the same field in the previous record,
press CTRL+”. If you have access to a computer with projection equipment, consider
demonstrating these methods to the class. Use Figure 3-7 to describe updating the contents of a
If students find that while typing data into a field, they are overwriting the existing data instead
of inserting data, it is because they are in Overtype mode rather than Insert mode. Use the INSERT
key on the keyboard to toggle between Overtype mode and Insert mode.
When does the change to a field become permanent? (Answer: When you move to another
record or close the table)
Switching between views, AC 119
Use Figures 3-8 and 3-9 to describe switching from Form view to Datasheet view. Emphasize
that the position in the table is maintained when switching between Datasheet view and Form
view. Discuss More About The View Button. Consider Other Ways to switch from Form view to
What views are available on the view list in Microsoft Access? (Answer: Design, Form,
Datasheet, PivotTable, PivotChart)
Page 4 of 13 Project 3: Maintaining a Database Using the Design
and Update Features of Access
Filtering records, AC 120
Define filter. Explain the difference between using the Find button and using a filter. Emphasize
that you can use filters either in Form view or Datasheet view.
Using filter by selection, AC 121
Define Filter By Selection. The filter by selection method produces a subset of the table. This is
useful when you need to update a field in several records with the same value. Use Figures 3-10
and 3-11 to describe using filter by selection. You also can use filter by selection in Form view.
Use Figure 3-12 to explain removing a filter and point out that if there is no filter applied to the
table, the Remove Filter button displays the ScreenTip, Apply Filter. Discuss Other Ways to use
filter by selection.
If students cannot see all of the records in their database table, it may be because a filter has been
applied. Clicking the Remove Filter button will cause all filters to be removed, so that all records
can be displayed.
Using filter by form, AC 122
Define Filter By Form. Point out that you use filter by form to retrieve records based on more
than one value. When you click the arrow in the field, all possible values for the field appear.
Use Figures 3-12 and 3-14 to illustrate using filter by form. Discuss Other Ways to use filter by
How does Filter By Form differ from Filter By Selection? (Answer: Filter By Form lets you filter
records based on more than one field)
Using advanced filter/sort, AC 123
Define Advanced Filer/Sort. Advanced filters are very similar to the queries that students did in
Project 2. Use Figures 3-15 through 3-18 to explain using Advanced Filter/Sort. Point out the
Apply Filter button and the compound criteria in Figure 3-17. Discuss Figure 3-18. Emphasize
that the Apply Filter button is now the Remove Filter button. Discuss the Q&A on page AC 125.
Deleting records, AC 125
Explain that when you delete the records, they are removed permanently from the database. In
some database management systems, deleting records is a two-stage process. First, records are
marked for removal. The records remain in the table but cannot be updated and will not be
retrieved in searches. Records are removed permanently from the database in a separate step. It is
important to back up a database before adding, changing, or deleting records. To use the
keyboard to delete a record, press CTRL+MINUS SIGN (-). Use Figures 3-19 and 3-20 to explain
Access 2003 Page 5 of 13
deleting a record. Point out the dialog box in Figure 3-20. Review the steps on page AC 126 to
delete a record in Form view. Discuss Other Ways to delete a record.
Changing the structure, AC 126
Define structure. Consider the various reasons why the structure of a database might need to be
changed. Discuss the benefits of the changes to the Client table. Use More About Changing the
Structure to point out the advantages of using a database management system.
Ask students for other reasons to change a database structure.
Changing the size of a field, AC 127
Point out that the size of a field can be increased or decreased. If the field size is decreased, any
existing data larger than the new field width will be truncated. Use Figures 3-21 through 3-24 to
describe changing the size of a field.
Adding a new field, AC 128
Consider the reasons why a new field may be necessary. Discuss the Q&A. Use Figures 3-25
through 3-27 to explain adding a new field. Mention Other Ways to add a new field. Discuss
More About Moving a Field in a Table Structure and More About Changing Data Types on page
130. Point out the Q&A on page AC 131.
Ask students for other examples of when it might be necessary to change a data type.
Deleting a field, AC 130
Remind students that there are times when fields are no longer needed. Review the steps to
delete a field.
Updating the restructured database, AC 130
Point out how easy it is to change the characteristics of fields. Use Figure 3-28 to explain
updating the contents of a field.
Changing the appearance of a datasheet, AC 131
Mention the various ways that you can change the appearance of a datasheet.
Resizing columns, AC 131
Define resizing, field selector, and best fit. Explain why you might need to resize columns in a
table. Point out that More About Resizing Columns on page AC 136 discusses changes to field
Page 6 of 13 Project 3: Maintaining a Database Using the Design
and Update Features of Access
sizes on a form. Use Figure 3-29 through 3-32 to explain resizing a column. Use Figure 3-33 to
point out that resizing a column or a row is a change to the layout of the table. Discuss Other
Ways to resize a column on page AC 133.
Formatting a datasheet, AC 133
Point out that you can change the font size, style, and color as well as the cell effects. Use
Figures 3-34 and 3-35 to explain changing the font in a datasheet. Consider Other Ways to
change the font. Use Figures 3-36 and 3-37 to illustrate changing the format of the datasheet
grid. Use Figure 3-38 to explain using print preview. Discuss the Q&A on page AC 135. Review
Steps 1 and 2 on page AC 135 to close the datasheet without saving the format changes.
Mass changes, AC 135
Review the query types that you can use to make mass changes to records in a database. Stude nts
will use update, delete, append, and make-table queries in the exercises at the end of this project.
Discuss More About Action Queries.
What are the four types of action queries? (Answer: Delete, Update, Append, Make-table)
Using an update query, AC 136
Define update query. Point out the benefits of using update queries rather than Datasheet view
or Form view to make the entries; it is easier and less prone to mistakes. Use Figures 3-39
through 3-41 to explain using an update query to update all records. In Figure 3-41, clicking No
would stop the procedure and no records would be updated. Discuss Other Ways to create an
Using a delete query, AC 137
Define delete que ry. Use Figure 3-42 through 3-44 to describe using a delete query to delete a
group of records. In Figure 3-44, if you select No the record will not be deleted. Once records are
deleted using a delete query, the operation cannot be undone. To preview the records to be
deleted, view the query in Datasheet view before running it. Discuss the Q&A. Consider Other
Ways to create a delete query on page AC 139.
Using append and make-table queries, AC 139
Define append query and make-table query. Review the steps to create an append query.
Mention the difference between an append query and a make-table query. An append query adds
records to an existing table. A make-table query adds records to a new table.
Access 2003 Page 7 of 13
Validation rules, AC 139
Define validation rules and validation text. Explain require d field, range of values, and
default value. Consider the importance of including validation rules in the database. Mention
that including validation rules also makes it easier to enter data. When a data type is declared as
Number or Currency, Access automatically validates the type of data that can be stored in the
field (only numbers can be entered in the field).
Ask students if they have ever been affected by invalid data in a database. If so, how?
Specifying a required field, AC 139
Use Figures 3-45 and 3-46 to describe specifying a required field.
Ask the class to name some possible reasons for making a field required.
Specifying a range, AC 140
Use Figure 3-41 to illustrate specifying a range using a validation rule and validation text. You
also can specify just a minimum or maximum value.
Specifying a default value, AC 141
Use Figure 3-48 to describe specifying a default value.
Specifying a collection of legal values, AC 142
Define legal values. The use of legal values is very useful when there are a limited number of
values available for a field. Use Figure 3-49 to describe specifying a collection of legal values.
Note that Access automatically inserts the quotation marks around text values and changes the
lowercase letter o to uppercase in the word, or. Discuss More About Validation Rules.
Using a format, AC 143
Define format and format symbol. Use Figure 3-50 to explain specifying a format. Formats
affect only the appearance of the data and not how it actually is stored.
What does the Format symbol “<” ask Access to do? (Answer: Display all text as lowercase)
Saving rules, values, and formats, AC 144
Use Figures 3-51 and 3-52 to explain saving the validation rules, default values, and formats.
Point out in Figure 3-52, that Access can apply the rules to already existing data.
Page 8 of 13 Project 3: Maintaining a Database Using the Design
and Update Features of Access
Updating a table that contains validation rules, AC 145
Use Figures 3-53 through 3-57 to illustrate the effect validation rules have on data entry.
Students can test the validation rules by copying their database and making intentional errors to
the copied database. Remind students that pressing the ESC key will undo any changes to the
If students find themselves stuck in a record when attempting to update it, because Access says
the value is invalid, and they cannot find a way to correct the value, this probably means that the
validation rule being referenced is faulty. Carefully note which validation rule is causing the
problem. Press the ESC key to completely erase the new record. Then, check and correct the
offending validation rule.
When a validation rule is violated, Access displays the text in the Validation Text property box.
Why is it important that this text is helpful to the user?
Creating a lookup field, AC 146
Define Lookup field. Explain the advantages of a lookup field. Users can select from a list of
values. Use Figures 3-59 through 3-62 to explain using the Lookup Wizard. Point out the
Lookup Wizard data type in Figure 3-58. Use Figure 3-63 to point out that the data type for the
Client Type field is still Text because the values entered in the wizard were text values. Discuss
More About Lookup Fields on page AC 150.
Using a lookup field, AC 149
Use Figures 3-64 through 3-66 to illustrate using a lookup field to change a value for a field.
Referential integrity, AC 150
Define referential integrity and foreign key. Spend some time explaining these concepts to
students. These concepts can be related to the concept of redundancy that was discussed in
Project 1. Using more than one table in a database eliminates redundancy but there needs to be
some way to link the tables and prevent errors. If you are using the Access 2003 Comprehensive
text, Appendix F further explains referential integrity and foreign key. All relational database
management systems must have the ability to enforce referential integrity. Discuss More About
Referential Integrity on page AC 154.
PROJECTS TO ASSIGN
Have students research referential integrity and write a report detailing their findings. The report
(1) What it is
(2) Why it is important
Access 2003 Page 9 of 13
(3) What makes supporting it in a relational database difficult
(4) How developers solved the problems involved in supporting it
Specifying referential integrity, AC 150
Define one-to-many relationship. Point out that in the Ashton James College database, there is
a one-to- many relationship between the Trainer table and the Client table. One trainer is
associated with many clients. Define cascade the delete. Point out that normally Access
prohibits the deletion. Define cascade the update. Again, Access normally prohibits the update.
Use Figures 3-67 through 3-71 to explain specifying referential integrity. Emphasize that it is
necessary to be in the Database window to define relationships. Use Figure 3-70 to point out the
Cascade Update Related Fields and the Cascade Delete Related Fields check boxes. Define
relationship line. Use Figure 3-71 to point out the relationship line. Discuss Other Ways to
specify referential integrity. Use Figures 3-72 and 3-73 to discuss the importance of referential
integrity in keeping a database free from update problems. Discuss the Q&A on page AC 154.
Point out More About Relationships: Printing Relationships on page AC 151. Students print the
Relationships window for all the exercises at the end of this project. Discuss More About
Relationships and Lookup Wizard Fields on page AC 149.
Using subdatasheets, AC 153
Define subdatasheet. Point out the advantages of being able to display the one-to- many
relationship between the Trainer and Client tables. Use Figures 3-74 and 3-75 to illustrate using a
Finding duplicate records, AC 154
Describe the purpose of the Find Duplicates Query Wizard and the reasons for using such a
wizard. Review the steps to find duplicate records.
Finding unmatched records, AC 154
Describe the purpose of the Find Unmatched Query Wizard and the reasons for using such a
wizard. Review the steps to find unmatched records.
Orde ring records, AC 155
Point out that, by default, records are in order by the primary key. Use Figure 3-76 to point out
that the client names are not in alphabetical order (Center Services comes before Calder
Plastics), but the client numbers (the primary key) are. Discuss More About Changing Table
Properties on page AC 159. The OrderBy property allows a user to change the default table
order. Cases and Places 3 has students change the default order of the Book table.
Page 10 of 13 Project 3: Maintaining a Database Using the Design
and Update Features of Access
Using the sort ascending button to order records, AC 155
Emphasize that the procedures for ordering records are different depending on whether it is a
single- field sort or multiple-field sort. Use Figures 3-76 and 3-77 to illustrate using the Sort
Ascending button. Discuss Other Ways to sort the records in ascending order.
Ordering records on multiple fields, AC 156
Use Figures 3-78 and 3-79 to illustrate using the Sort Ascending button to order records on
multiple fields. Use Figure 3-78 to describe selecting multiple columns. Use Figure 3-79 to
explain using the Sort Ascending button to order records on multiple fields. To change the order
of columns in a table, click the field selector for the column and drag to a new location. To
redisplay records in order by the primary key, click Remove Filter/Sort on the Records menu.
Creating and using indexes, AC 157
Define index and index key. Discuss More About Indexes. Make sure students understand the
benefits of indexing. Explain that indexes are used both for retrieving records quickly and listing
records in a different order. Discuss the indexes and tables shown in Figures 3-80 and 3-81.
How does Access use an index? AC 159
Explain that indexes can optimize the performance of the database. Make sure students
understand that Access uses indexes automatically. In some database managements systems, the
user must indicate that an index is to be used.
When should you create an index? AC 159
Discuss the four conditions that determine when to create an index. Consider that indexes slow
the editing, adding, and deleting of data.
Divide the class into small groups. Ask each group to read together the four conditions under
which an index should be created, from page AC 159 of the text. Then, ask each group to make a
list of when NOT to create an index. Allow each group to share its list.
Creating single-field indexes, AC 159
Define single-field index. Use Figure 3-82 to describe creating a single- field index.
Creating multiple-field indexes, AC 159
Define multiple-field indexes and Indexes button. Use Figures 3-83 and 3-84 to describe
creating a multiple- field index. Discuss Other Ways to create an index.
Access 2003 Page 11 of 13
Closing the database and quitting Access, AC 162
Review Steps 1 and 2 to close a database and quit Access. Discuss More About The Quick
Special database operations, AC 162
List the special operations involved in maintaining a database. The ability to backup a database
that is open is a new feature in Access 2003.
Backup and recovery, AC 162
Define recover, backup copy or save copy, backing up, and live database. Point out that the
ability to backup an open database is a new feature in Access 2003. Review the steps on page
AC 163 to backup a database.
Databases should be backed up periodically. What factors determine how frequently to back up a
Compacting and repairing a database, AC 163
Define compact and re pair. Point out that when you delete an object, the space occupied by the
object does not become available. Pictures can increase the size of a database significantly.
Characterize the typical three-step process for compacting a database. Review steps on page 164
to compact and repair a database. Discuss More About Compacting and Repairing a Database
and More about Backup and Recovery.
If Access displays an error message when students compact a database on a disk in drive A, there
probably is insufficient room (the message does not clearly indicate this is the problem) to
perform the compacting process. The best way to compact a database is first to copy the database
to a network disk or a hard disk. Then, compact the database, check to make sure that it is usable,
and copy it over the original database.
Project summary, AC 164
Briefly review the material presented in this project. If students have a SAM user profile,
encourage them to log in to their SAM account and go to the assignments page for additional
What you should know, AC 164
Encourage students to use this section in preparing for tests and quizzes. Discuss More About
Microsoft Certification on page AC 162.
Page 12 of 13 Project 3: Maintaining a Database Using the Design
and Update Features of Access
Learn it online, AC 166
These exercises ask students to use the Web for additional activities, information, and resources
related to topics presented in this project. Have students use their browsers and the given URL to
complete selected exercises.
Apply your knowledge, AC 167
This exercise gives students a chance to use what they have learned in this project with a
document on the Data Disk. Exercise 1 can be reviewed and assigned at this time.
In the lab, AC 168
These exercises provide students with practice in using the skills developed in this project.
Exercises 1 through 3 can be reviewed and assigned at this time.
Cases and places, AC 171
These exercises offer students the opportunity to learn more about Access 2003 through open-
ended activities with varying degrees of difficulty. Students can be assigned one or more
exercises or be allowed to choose the exercises in which they are most interested.
Access 2003 Page 13 of 13
Advanced Filter/Sort (AC 123) legal values (AC 142)
append query (AC 139) live database (AC 162)
backing up (AC 162) Lookup field (AC 146)
backup copy (AC 162) maintaining the database (AC 114)
best fit (AC 131) make-table query (AC 139)
cascade the delete (AC 150) multiple- field indexes (AC 160)
cascade the update (AC 151) one-to- many relationship (AC 150)
compact (AC 163) Overtype mode (AC 119)
default value (AC 139) range of values (AC 139)
delete query (AC 137) recover (AC 162)
delete the records (AC 125) referential integrity (AC 150)
field selector (AC 131) relationship line (AC 152)
filter (AC 120) repair (AC 163)
Filter By Form AC 122) required field (AC 139)
Filter By Selection (AC 121) resizing (AC 131)
Find Duplicates Query Wizard (AC 154) restructure the database (AC 114)
Find Unmatched Query Wizard (AC 154) save copy (AC 162)
foreign key (AC 150) searching (AC 117)
Form view (AC 116) single- field index (AC 159)
format (AC 143) structure (AC 126)
format symbol (AC 143) subdatasheet (AC 153)
Index (AC 157) update query (AC 136)
index key (AC 157) validation rules (AC 139)
Insert mode (AC 119) validation text (AC 139)