Access 2007: Comprehensive Hands-On Introduction

Course Overview

Today's database solutions begin on the desktop with Excel, naturally evolve to Access and ultimately to enterprise-wide applications. Microsoft Access 2007 is a powerful visual tool with which to design and develop these applications.

This hands-on course provides an in-depth view of the features and functionality of Access 2007. You learn to upsize Excel spreadsheets, develop and support robust Access systems, manipulate and query data, develop forms and design sophisticated data analysis reports.

Audience

This course is valuable for anyone who needs to manipulate, analyse or report on data. Familiarity with databases or macros is helpful.

Skills Gained

• Utilise Microsoft Access 2007 to design robust database applications
• Apply Form, Table, Report and Query wizards to quickly build database applications
• Create and integrate macros into your applications
• Quickly modify forms and reports with selective filtering, sorting and grouping
• Implement advanced Access reporting features
• Link to SharePoint and SQL Server data systems

Course Outline

Microsoft Access 2007 Overview

Discovering the interface

• Browsing by category in Help
• Quick data functions in reports
• Exploring the task-based ribbon

Overview of database concepts

• Databases and their contents
• Flat file and relational databases

The fundamentals of database design

• Tables, records and fields
• Implementing database normalisation
• Ordering schemes
• Indexes
• Setting compound primary keys

Building a Relational Database with Access

Defining the requirements

• Eliciting user needs and wants
• Designing the data model

Creating an Access database system

• Access tables, fields, indexes
• Table relationships
• Modifying field functionality with properties

Integrating internal and external data

• Customising forms with the Layout View
• Attaching or importing existing tables
• Designing a friendly user interface
• Form controls and properties

Querying Relational Data

Query types and elements

• Defining queries
• Choosing fields in the Design View
• Select, Action and Parameter queries

Developing queries

• Query wizards
• Expressions in queries
• Refining data output with sorting and filtering

Access Reports for Data Analysis

Using Report wizards

• Generating quick reports
• Tabular and column report types
• Visually enhancing your presentation with conditional formatting
• Exploiting formulas and expressions in reports
• Sorting out groups and totals

Modifying the standard report formats

• Hiding data detail in summary reports
• Elements of effective custom reports
• Adjusting report controls
• Publishing to PDF and XPS formats

Task Automation with Macros

Designing effective macros

• Point-and-click
• Debugging tips
• Macro style guidelines
• Running a macro from events

Event-driven programming

• Linking forms and reports
• Associating macros with controls to respond to user-triggered events
• Triggering a macro from a hyperlink

Access as a Multiuser Front-End

Data separation in Access

• Creating a non-data ACCDB
• Strategically placing your data

Linking to SharePoint and SQL Server

• The ODBC connectivity standard
• Utilising SharePoint and SQL Server data within Access
• Enabling users to link to SharePoint lists

Advanced Database Settings

• Condensing and refining data with PivotTables
• Automatic database compacting
• Analysing application performance
• Encrypting and password protecting your database

Access 2007: Comprehensive Hands-On Introduction / MAC07-123