This scenario-based class
provides experienced Microsoft Access users with a stronger relational database
foundation. It brings home relational database concepts and teaches how to
employ powerful techniques using Access in gathering, organizing, and reporting
of information vital to your business. Students will learn how to create solid
database structures, how to pull in outside data, and how to integrate Access
with other Microsoft Office System 2003 applicationsall leading up to creating
flexible, user-friendly reports. The goal of this course is to give students
more confidence in managing their business information using various Access
database techniques, thus enabling them to accomplish their daily work more
efficiently and competently.
This course is designed for
experienced Access users. This audience uses Access to store information about
their business and is skilled at using tables in datasheet view, creating their
own forms and reports, and even writing queries. While understanding how to
create these Access objects, these students are looking for additional methods
for processing data, including importing and linking to data existing outside
of Access.
After completing this
course, students will be able to:
|
|
Create solid relational
database structures using Access for data integrity that is easier to
maintain. |
|
|
Import data of various
formats from outside sources. |
|
|
Utilize appropriate
techniques for querying information in the Access database as various
business needs arise. |
|
|
Create consistent
standard user interfaces for inputting data. |
|
|
Generate powerful and
flexible reports for management. |
|
|
Describe the benefits
that can be realized from the integration of Access with other Office 2003
applications. |
Before attending this
course, students must have:
|
|
A basic understanding of
relational databases. |
|
|
An understanding of
Access tables, queries, forms and reports and a base knowledge of how they
are used within an Access database. |
|
|
Experience creating
Access macros and using them in forms. |
|
|
A working familiarity
with standard Microsoft Office applications (such as Microsoft Outlook, Word,
and Excel). |
Module 1: Creating a
Strong Foundation for Your Database
This module introduces the
concepts of relational database design, including creating tables and
relationships, as well as importing data into tables from various external
sources.
Topics and Activities
|
|
What Is a Relational
Database? |
|
|
Demonstration: Creating
Tables and Using the Relationships Window |
|
|
Expert Video: Importance
of Using Relationships and Referential Integrity |
|
|
Types of Files Commonly
Imported or Linked to Access |
|
|
Exercise: Importing a
Text File and Excel Data |
|
|
Best Practices for
Creating Tables and Utilizing Outside Data |
After completing this
module, students will be able to:
|
|
Describe relational database concepts. |
|
|
Create tables and relationships. |
|
|
Import data into tables
from different types of file sources. |
|
|
Incorporate best
practices for creating tables and utilizing outside data. |
Module 2: Creating an
Effective User Interface
This module introduces form
design standards and creating interfaces. Creating switchboards and utilizing
the Form Wizard to create a base and modify a form is also discussed in this
module.
Topics and Activities
|
|
Good Form Design |
|
|
Mapping Out the Forms to
Use in the Fourth Coffee Database |
|
|
Exercise 1: Creating the
Necessary Input Forms |
|
|
Exercise 2: Creating the
Main Switchboard and Setting Startup Properties |
|
|
Best Practices for
Creating User Interfaces |
After completing this
module, students will be able to:
|
|
Make intelligent
decisions regarding the use of form standards. |
|
|
Create switchboards for
launching tasks in their databases. |
|
|
Use a Form Wizard as a
base for creating forms. |
|
|
Enhance forms so that
users can work with them more effectively. |
Module 3: Organizing
Data Though Queries
This module introduces the
advantage of different types of queries to organize and provide views of data
in various ways as necessary for a business. In addition, the use of parameters
and practice to retrieve only the data the students need are covered in this
module. Finally, this module shows students how to display parameterized
queries using a Combo Box located on a form.
Topics and Activities
|
|
Choosing the Right Type
of Query for the Task at Hand |
|
|
Exercise 1: Creating
Select and Totals Queries |
|
|
Retrieving Only the
Information That You Want |
|
|
Exercise 2: Adding Criteria to Queries |
|
|
Exercise 3: Using Forms
and Controls for Criteria |
|
|
Exercise 4: Adding Data
by Using an Append Query |
|
|
Best Practices for Using
Queries to Their Fullest Potential |
After completing this
module, students will be able to:
|
|
Use Query Design view. |
|
|
Specify and create
various types of queries. |
|
|
Add parameters and
criteria to query requests. |
|
|
Create forms that call
queries. |
Module 4: Reporting
Strategic Information with Access
This module discusses
reporting strategic information using Access by showing techniques for calling
reports from forms and by adding macros to turn on and turn off reports to
display the data users require.
Topics and Activities
|
|
Generating Reports That
Display Strategic Information |
|
|
Exercise 1: Generating
the Base Report |
|
|
Demonstration: Touring
Report Design View |
|
|
Exercise 2: Creating a
Macro to Companies Only on the Report |
After completing this
module, students will be able to:
|
|
Generate a report by
using the Report Wizard. |
|
|
Construct a form for
calling the report and displaying the report in Print Preview mode for
printing. |
|
|
Hide and display sections
of a report. |
Module 5: Integrating
Access with other Office Applications
This module shows how to
take information contained within your Access database and merge it with other
Office applications.
Topics and Activities
|
|
Integration with Other
Office Applications |
|
|
Exercise 1: Publishing
Information Using Word and Excel |
|
|
Exercise 2: Creating a
Custom Toolbar for Office Links and Mail Recipient |
|
|
Exercise 3: Performing
Mail Merge Between Access and Word |
|
|
Best Practices for
Avoiding the Pitfalls of Integration |
After completing this
module, students will be able to:
|
|
Recognize how easily
Access and other Office products integrate. |
|
|
Publish an Access report
to Word. |
|
|
Export a table to Excel. |
|
|
Employ a custom toolbar
that sends a report in e-mail. |
|
|
Perform a mail merge
between Access and Word. |
F. Scott Barker, a
Microsoft Access MVP, is the author of numerous books on Microsoft Access, most
notably F. Scott Barkers Access Power Programming 2002 and Microsoft Access
2003 Visual Blueprint. Through his company AppsPlus, Scott consults for clients
in many industries, including Toyota, Microsoft, and Exterior Research, Inc.