Beyond the Spreadsheet: Managing Financial Information Using Microsoft Office Access 2003
(Microsoft Training Course: 4005) - 0.5 day - £600 exc VAT

The techniques learned in this course will enhance skills for gathering, organizing, and reporting vital business information and demonstrate the power of Access as a financial data management and analysis tool. This course is designed for people who use Access on a regular basis, and who are also experienced with Excel.

1. Starting with a Firm Relational Foundation
2. Using Queries to Work with Financial Information
3. Generating Flexible Reports for Management Use
4. Introduction to Microsoft Visual Basic for Applications
5. Automating Excel from Access Using VBA

---

---

Module 1: Starting with a Firm Relational Foundation

• 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

Skills

• 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.
  

top

---

Module 2: Using Queries to Work with Financial Information

• 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

Skills

• 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.
  

top

---

Module 3: Generating Flexible Reports for Management Use

• 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

Skills

• 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.
  

top

---

Module 4: Introduction to Microsoft Visual Basic for Applications

• 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

Skills

• 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.
  

top

---

Module 5: Automating Excel from Access Using VBA

• 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

Skills

• 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.
  

top

---

Before attending this course, students must have the following pre-requisites:

• 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 Excel and Microsoft Word).

After completing the course, students will be able to create solid relational database structures using Access for data integrity that is easier to maintain; utilize appropriate techniques for querying information in the Access database as various business needs arise; generate powerful and flexible reports for management; use VBA to create routines to automate simple repetitive tasks in Access Create; integrate their data with Microsoft Excel by using the built-in features of Access and by using VBA code.