Chapter 1
Introduction to Microsoft Access
Notes to the Instructor
This chapter introduces the Microsoft Access 2010 database management software. Students will
learn about using tables, displaying database information, finding answers to questions with
database queries, using forms, and printing database reports. Some students already have an
adequate working knowledge of Microsoft Access and therefore this chapter may be unnecessary
review for them. We suggest that you poll your students to determine their experience levels
before proceeding. If you determine that the class has sufficient experience, it may be best to
proceed to Chapter 2, Databases and Accounting Systems.
Lecture Outline
The pages that follow provide an outline of the contents of Chapter 1.
Objectives
• Understanding the Access work environment.
• Creating and using Access objects including tables, queries, forms, and reports.
• Customizing the Access environment.
• Opening and displaying tables.
• Retrieving information with queries.
• Modifying tables’ contents with action queries.
• Creating and using forms to display data.
• Designing and using database reports.
1-2 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
Introduction
What is Microsoft Access?
• A relational database management system that supports small to medium-sized database
applications. Microsoft Corporation produces the database system.
What is a Relational Database?
• Dr. E.F. Codd, working for IBM at the time, is the acknowledged father of modern relational
database management systems.
• A relational database system normally contains tables, queries, forms, and reports.
Starting Microsoft Access
• Access 2010 opens in Backstage View.
• Backstage View is where you do thing to your databases, whereas the Access client is where
you deal with a particular database.
Obtaining Help
Exercise 1.1: Displaying Help. This exercise shows students how to locate help. You might
want to ensure that they understand how to seek context-specific help so that they can be self-
sufficient when expert human advice is unavailable. Search for a couple of different terms to
illustrate how help works. Also illustrate that the Access toolbar has ToolTips, which are
displayed when the mouse hovers over any of the toolbar icons.
Printing Help
• Locate the help screen you would like to print.
• Choose the desired help topic.
Exiting Access
• Exiting Access updates all changes to the database, closes the database, and returns to the
desktop or another suspended program.
CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS 1-3
Examining the Access Environment
Exercise 1.2: Opening the Chapter 1 Database. This exercise shows the students how locate
and open an existing database—the one associated with Chapter 1 Please refer to the textbook
exercise.
Access Work Surface
• The Access Ribbon
➢ Known as Office fluent interface.
➢ Contains command tabs with groups of commands by usage.
➢ Home command tab contains commands to format tables (text, font, etc.), create, save,
Exercise 1.3: Setting Access Options. This exercise ensures that all students’ Access
programs have the same global settings such as a tabbed document window, nonoverlapping
windows, compact on close, and remove personal information.
• The Quick Access Toolbar provides a fully customizable list of icons that you set for easy
access to often needed command buttons.
Access Objects
• Objects are the structures you create and methods you employ to maintain and display your
data.
• Objects described in this chapter include tables, queries, forms, and reports.
• Tables (see Figures 1.3 and 1.4, for example):
➢ Hold all data in the database. (Stress to the students that only tables hold the data—no
other of the Access objects, including queries, forms, or reports, actually holds data.)
➢ Are two dimensional and have rows and columns.
1-4 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
• Forms (Figure 1.9):
➢ Provide a way to view table data one row at a time.
➢ Facilitate data entry for inexperienced users.
➢ Display data from tables or queries but do not actually hold data.
Working with Databases and Tables
Opening a Database
Exercise 1.4: Opening the Chapter 1 Database. This exercise shows the students how locate
and open the Chapter 1 database. Although Exercise 1.3 did this already, this serves as an
introduction to creating a backup of a database. Please refer to the textbook exercise.
Creating a Backup of an Access Database
This is particularly important for students to understand in case they create a database from
scratch and don’t pay attention to the default folder where Access stores the data. By opening the
database from the frequently used list on the opening window, students can then create a backup
and thereby save the database on their flash drives or other known location. A Try it exercise
reinforces, with steps, creating a backup (copy) of an open database.
Looking at Data through Different Tabs
• Data can be viewed and inspected through a table’s Datasheet view, a form, a query’s
Datasheet view, or a report—individually or simultaneously. In addition, you can view both a
Opening a Table
Emphasize that there are usually several different ways to accomplish a task with Access.
Opening a table provides is no different: Double-click a table’s name, right-click and select
Open, or drag the table’s name to the Access work area.
Exercise 1.5: Opening a Table. This exercise shows the students how to open the
and 1.16 have good graphics that you can point to while explaining the fluent interface ribbon
and its contents.
CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS 1-5
Moving Around a Table
• When you display a table, there are several ways to move from one record to the next. You
can:
➢ Select the Home tab, click the Go To command, and then click First, Previous, Next,
Last, and New.
Searching for a Value in a Column
• Use the Find command to locate a specific field value in a large table.
• Exercise 1.6 shows you how.
Exercise 1.6: Searching for a Row Containing a Particular String. This exercise shows the
student how to quickly locate a customer whose street address contains a particular string.
Please refer to the textbook exercise for details of how to do this.
• Discuss with students what happens if the search value cannot be found so they understand
the difference between “successful” and “unsuccessful” searches.
Changing a Table’s Display Characteristics
• You can change a table’s display characteristics by choosing one or more columns and then
clicking the right mouse button; the table’s structure remains unchanged.
Exercise 1.7: Changing a Table’s Display Characteristics. This exercise shows students
how to change a column’s displayed position and width in the tblInvoices table (Figure 1.17).
Remind students that changing a table’s display characteristics including column positions,
widths, etc. is a cosmetic change that does not affect the underlying table’s column order or
other properties. Please refer to the textbook exercise.
Sorting and Filtering Table Rows
• You can locate a particular record much more easily when the table’s records are sorted and
when they are filtered to display only a particular subset based on one or more criteria.
• You can use a quick sort to organize a table’s rows based on one or more columns; sorting
commands are found on the Sort & Filter group of the Home command tab.
Exercise 1.8: Sorting and Filtering Table Rows. This exercise shows students ways to sort
the tblInvoices table in ascending order by the Shipper column. The column drop-down list is
1-6 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
used to select a unique ship date from the list. The filter restricts the rows to that date. Please
refer to the textbook exercise.
Explain the Toggle Filter and Clear All Sorts buttons and their use. The Sort & Filter
group of the Home command tab holds those commands.
Printing a Table
• Print a table by selecting the table (open or not) in Datasheet view and then accessing
printing commands through the Backstage View. It is accessible by clicking the File tab.
• Choose any desired options in Print dialog box and then click OK.
Printing a Table’s Structure
• Print a table’s structure is a less-often used activity, but it is necessary at times.
• Click Database Tools and then lead the students through a small example of making
selections in the Database Documenter found in the Analyze group.
Querying a Database
• Querying a database is posing a question which returns an answer in the form of a virtual
table known by the name Access-specific name of dynaset.
• A query is a stored definition that specifies all tables involved in data retrieval, which
columns are to be retrieved, which records are to be included in the result, and any
calculations to be performed (see Figure 1.19).
Using a Query
Exercise 1.9: Running a Query. This exercise shows students how to open and run an existing
query, qryArcataCustomers. Remind students that they can run an existing query—one that is
stored in the Queries category on the Navigation Pane. Right-click the query name and click
Open or by double-clicking the query name. (We prefer the latter method because it involves
fewer actions.) Please refer to the textbook exercise for further details.
Creating a One-Table Query
• Queries that restrict which rows are returned by using some criteria (e.g., customer invoices
over 59 days old) are the basis of a fundamental relational database operation called selection
(you specify which rows to select).
CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS 1-7
Exercise 1.10: Creating a One-Table Query. This exercise shows the students how to search
for and select a specific customer from the tblInvoices table and return rows based on a date
filter and a shipper filter—all supplied in the criteria row of the QBE grid. (See Figures 1.20 and
1.21.)
Be sure to point out to students that date values must be contained between # symbols.
They don’t always pay attention to that detail, and Access will not compensate for their
omission.
Saving a Query
• Queries can be saved and re-executed periodically to produce current lists of clients, spare
parts, invoiced over 60 days past due, etc., reflecting changes in the underlying table(s) over
time.
• With more than one query open, only the active query—the one whose tab is active—is
saved.
Sorting the Results
• The normal, default row order of a dynaset is ascending order by the table’s primary key.
• If a table has no primary key, then table rows are displayed in no particular order.
• A dynaset can be sorted by any field or combination of fields by indicating which columns
are the sort columns in the query’s definition.
Using More Complex Selection Criteria
• Queries that must satisfy more than one condition, with all conditions being true, use the
AND operator (e.g., discount percentages AND how many occurred in January).
• Figure 1.23 shows a list of comparison operators used by Access to compare two strings or
numerical values.
Exercise 1.11: Writing a Query with an “AND” Operator. This exercise shows the students
how to use the AND operator to query and display invoice lines with discount percentages
greater than zero and less than 11 (Figure 1.22). Be sure to point out to students that numeric
criterion values are not enclosed in quotation marks.
Creating Selection Criteria Using the “OR” Operator
1-8 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
Exercise 1.12: Forming a Query with “OR” Criteria. This exercise shows students how to
form a query involving two different criteria columns of the tblProducts table (Figure 1.25). Walk
through this example in class. It is a good example of a relatively complex query involving only
one table.
• Explain the use of the wildcard character * on each end of choc so that the string “slides”
across all description strings for a match anywhere in the string.
• Queries having two or more independent selection criteria column values have as many
criteria rows in the query grid as there are individual criteria for which any one being true is
sufficient to satisfy the condition.
• Queries with alternate selection criteria on one field (e.g., “Chocolate” Or “Vanilla” in the
Including Expressions in a Query
• Expressions are special “fields” that display the results of calculations in a dynaset. Of
course, expressions cannot be stored in a table, because a table stores only values
(invariants), not expressions (unlike a spreadsheet).
• Access allows an expression in a table field. We discourage using this because it violates
normalization rules and is, frankly, sloppy design.
Printing Dynasets
Printing a dynaset is straightforward:
• Display the dynaset.
• Check the dynaset to ensure it is what you expect.
• Click the File tab, click Print, and click Print.
• Make necessary selections in the Print dialog box and then click OK.
Using Forms
• Forms provide convenient, less cluttered work surfaces through which you can enter or alter
information in tables and queries.
• Forms can be created that facilitate data entry and mimic already familiar paper forms.
CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS 1-9
Viewing a Table through a Form
Exercise 1.13: Opening an Existing Form. This exercise shows students how to open and
move around the frmProducts form found in the Chapter 1 database Ch01.accdb (Figure 1.27).
• Form navigation buttons allow movement through rows of table.
• A form presents an intuitive and pleasant looking interface that is often familiar to a user.
Viewing a Query through a Form
• A Form can be created from a saved query—qryFebFedEx in this case (Figure 1.28).
• The total number of records displayed in a form—one after another—depends on how many
rows are produced by query.
Creating a Form Quickly
• A Try It exercise shows students how to create a quick form using the Form button in the
Create command tab Forms group. It is based on the tblCustomers table found in the Chapter
Saving a Form
• You save a newly created form design, not the data displayed within it, by clicking the Save
button on the Quick Access Toolbar. Any table data altered through a form is preserved
automatically by Access when you close a form, move to another record, or close Access.
• A Try It exercise shows students how to save a form.
Editing Data with a Form
• It is often easier to alter data in a table by using a form view of the table data.
• Usually, a form displays one record at a time.
• In Form view, click the field you want to change, make any changes, and move to another
record to post the changes. Alternatively, you cause the changes to be posted when you close
the form.
Exercise 1.14: Editing Data with a Form. This exercise shows students how to edit data using
the frmCustomers form. You can change a field by moving to it and then entering a new value.
• Remind students that changes made to a record are not posted to the database until you move
to another record. In the case of a form, simply press PgDn (or PgUp) or click Tab a
sufficient number of times to move to the next record.
Creating and Using a Split Form
• Split forms display data in two views on a single document window: a Form view on the top
1-10 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
Exercise 1.15: Creating a Split Form. This exercise shows students how to create a split and
altering the size of the panes by moving the form splitter bar. The split form is based on the
table tblCustomers and is shown in Figure 1.30.
Printing a Form
• Print one record by locating it within the form (use navigation buttons) and clicking the
Office Button, pointing to Print, clicking Print, choosing “Selected Record(s)” radio button,
and clicking OK in Print dialog box.
• Print a range of form pages by clicking the Pages option button and then entering the
beginning and ending page numbers to print.
Designing Reports
• You cannot enter or edit data in a report displayed in Print Preview.
• You can filter report data when the report appears in Report view.
• Reports are a way to produce hard copy output—to display database information—in an
orderly manner.
• Reports can be created ranging from simple utilitarian to professional designs with multiple
typefaces, drop shadows, graphics, and colors.
Previewing a Report
Exercise 1.16: Loading and Previewing a Report. This exercise shows students how to load
the existing report rptCustomerInvoices, examine it in Report view, and close it (Figure 1.31).
Creating a Report Quickly
• Creating a basic report of the data in a table or table is simple: Select the table name in the
Exercise 1.17: Creating a Report Quickly. This exercise shows students how to create a
basic report based on the table tblCustomers (Figure 1.32)
Saving a Report
• Right-click the report’s document tab and click Save.
Exercise 1.18: Saving a Report. This exercise shows students how to save a newly created
Exercise 1.19: Deleting a Report. This exercise shows students how to delete a saved report
from the Navigation Pane. Click the report’s name in the Navigation Pane. Press Del (keyboard
key), and click Yes in the confirming dialog box.
CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS 1-11
It may be easier for students to use the right-click menu for all Navigation Pane object operations
including delete. Right-click the object in the Navigation Pane and click Delete from the pop-up
menu. This is probably a better choice—right-clicking—because several alternative actions are
proposed in one pop-up menu.
Printing a Report
A Try it exercise leads students through opening a report and navigating to various report pages
Chapter 1 database.
Exercise 1.20: Closing the Database and Closing Access. This exercise shows students
how to close the database and close Access simultaneously
• Click the File tab and then click Exit.
1-12 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
Answers to Review Exercises
Multiple Choice Questions
1.
c
6.
c
Discussion Questions
The solutions presented here come from the textbook discussion. Your students may include other
insight that is relevant, but not presented in our solutions.
1. Discussion should involve at least mention of the Microsoft Fluent Interface—the ribbon, its
2. Occasionally, it is better to work with table data one row at a time. Tables are not a very
intuitive interface for some people, especially those who are not computer literate. Access
3. Two of likely several methods are these:
• Method One:
4. One of the real advantages of relational databases is the ability to ask questions that return
interesting and meaningful answers derived from a database. Relational database systems
make asking questions particularly easy, and Access is no exception. A query, the usual
2.
b
7.
3.
d
8.
c
4.
a
9.
5.
c
10.
CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS 1-13
5. Producing reports is as easy as clicking a button or two. Frequently, you will want to either
preview a report on screen or produce a printed report that you can pass around at a meeting
or keep as a permanent record. Access reports are just that—reports. You can neither enter
Practice Exercises
A note to the instructor: We have created, in the Navigation Pane of the instructor’s version of
the database, a custom category called Review Questions: Answers. That custom category
contains three custom groups: Practice Exercises, Problems, and Student Database. You will
find all the solutions to the practice exercises in the first group and all answers to the problems in
the second one. If the custom category Review Questions: Answers does not appear in the
Navigation Pane, click the category name at the top of the Navigation Pane and click the Review
Questions: Answers category name.
1. A solution is available on the instructor’s solution database in the Practice Exercises custom
group. It is called 1-Practice Exercise 1. (It is a copy of the tblCustomers table, but in the
2. A solution is available on the instructor’s solution database in the Practice Exercises custom
3. A solution is available on the instructor’s solution database in the Practice Exercises custom
group. It is called 1-Practice Exercise 3. Unlike students’ solutions, we have set the table
4. A solution is available on the instructor’s solution database in the Practice Exercises custom
5. A solution is available on the instructor’s solution database in the Practice Exercises custom
group. It is called 1-Practice Exercise 5. Open the Navigation Pane of your copy of the
1-14 CHAPTER 1 • INTRODUCTION TO MICROSOFT ACCESS
Problems
1. The answers to the three part question are stored in your database in the Problems custom
2. The answer to the question is stored in your database in the Problems custom group. It is
called 1-Problem 2. Open the form in Form view, click the Record Number text box, type
3. The answer to the question is stored in your database in the Problems custom group. It is