You are in Training / Microsoft / Professional Development / Course MS4005 / Course Outline

MS4005 Beyond the Spreadsheet: Managing Financial Information Using Microsoft Office Access 2003

Module 1: Starting with a Firm Relational Foundation

This module introduces the concepts of relational database design, including creating tables and relationships, as well as importing data into tables from various sources.

Topics and Activities

  		•

    Export Video: Importance of using Relationships and Referential Integrity

  		•

    Data Coming from Excel into Access

  		•

    Exercise: Importing Excel Data into Existing Tables

  		•

    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 Excel Workbooks and worksheets.

  		•

    Apply best practices for creating tables and utilizing outside data.

    Module 2: Using Queries to Work with Financial Information

    This module introduces queries you can use to work with financial information. The module also covers adding criteria to queries and utilizing parameters for criteria.

    Topics and Activities

  		•

    Looking at Queries Available for Working with Financial Data

  		•

    Exercise 1: Creating Select and Totals Queries

  		•

    Exercise 2: Working with Crosstab Queries

  		•

    Retrieving Only the Information You Want

  		•

    Exercise 3: Adding Criteria to Queries

  		•

    Best Practices for Using Queries to Their Full Potentials

    After completing this module, students will be able to:

  		•

    Identify what types of queries are available for working with financial data.

  		•

    Create financial queries.

  		•

    Work with Crosstab queries.

  		•

    Add criteria to queries.

  		•

    Apply best practices for using queries.

    Module 3: Generating Flexible Reports for Management Use

    This module introduces the use of Access reports to retrieve and display financial data using Microsoft PivotTable® and PivotChart® within Access reports. It also discusses using the grouping and sorting feature, and how to summarize data using these features.

    Topics and Activities

  		•

    Creating Financial Reports Using Access

  		•

    Exercise 1: Creating and Enhancing an Access Report

  		•

    Using Access PivotTables and PivotCharts

  		•

    Exercise 2: Displaying Data Using a PivotTable View

  		•

    Exercise 3: Displaying Data Using a PivotChart View

  		•

    Best Practices for Reporting Financial Information

    After completing this module, students will be able to:

  		•

    Use the Report Wizard to create a base for standard reports.

  		•

    Use the grouping and sorting feature for summarizing data.

  		•

    Use PivotTables for interactive reporting.

  		•

    Use PivotCharts for graphically reporting financial data.

  		•

    Apply best practices for reporting financial information.

    Module 4: Introduction to Microsoft Visual Basic for Applications

    This module introduces how to automate various tasks within Microsoft Access by using Visual Basic for Applications. You will examine the code created by Command Button Wizard, which allows you to see the basic structure of VBA procedures. You will also learn about the DoCmd object, which provides the majority of the functionality found in the macro actions.

    Topics and Activities

  		•

    Getting Started with Visual Basic for Applications by Using the Command Button Wizard

  		•

    Exercise 1: Gaining Experience with Visual Basic for Applications Procedures

  		•

    Exercise 2: Creating Event Routines Without Using Command Button Wizard

  		•

    Best Practices for Using Visual Basic for Applications Code in Databases

    After completing this module, students will be able to:

  		•

    Understand the code written by Command Button Wizard.

  		•

    Create code without using the wizard.

  		•

    Use Microsoft IntelliSense® to see what arguments are required for code.

  		•

    Use the DoCmd object to perform macro actions in code.

  		•

    Apply best practices for using VBA code in databases.

    Module 5: Automating Excel from Access Using VBA

    This module discusses the advantages of using Access and Excel together to manage financial information. This module also shows how to automate the moving of data into Excel from Access using VBA to provide greater control.

    Topics and Activities

  		•

    Automating Microsoft Excel from Access

  		•

    Exercise 1: Analyzing Information with Excel Using a Menu Command

  		•

    Exercise 2: Creating Visual Basic for Applications Code that Creates an Excel Worksheet

  		•

    Exercise 3: Loading a Recordset into Excel from Access

  		•

    Best Practices for Avoiding the Pitfalls of Automation

    After completing this module, students will be able to:

  		•

    Generate a report in Access and analyze it by using Excel.

  		•

    Use Visual Basic for Applications to create a routine to create an Excel workbook from Access.

  		•

    Use Visual Basic for Applications to load data into the Excel workbook from Access.

  		•

    Apply best practices for automating the process of moving data from Access into Excel.