Course Schedule:
Please call 8622 8900 or
email us for further information
Overview:
When people think of financial data such as invoicing, inventory, and sales information, they often think spreadsheets in Microsoft Excel. However, Microsoft Access is also a powerful tool in managing your financial information, especially as you collect more and more of it. With Access you can track and manage financial data just as you would other information such as customer lists, by using tools both within Access and cross-application with Excel. The techniques learned in this course will enhance skills for gathering, organising, and reporting vital business information and demonstrate the power of Access as a financial data management and analysis tool.
Learning Method:
Instructor Led.
Who will benefit from this course?
This course is designed for people who use Access on a regular basis, and who are also experienced with Excel. They understand how the various Access objects work individually and together, including Access macros, and they are familiar with Microsoft Excel Visual Basic Applications (VBA) macros.
Pre-requisites:
Before attending this course, you 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 Excel and Microsoft Word)
What can you expect to gain from this course?
After completing this course, you will be able to:
- Create solid relational database structures using Access for data integrity that is easier to maintain
- Utilise 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
Course Content:
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.
- 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 Utilising 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 utilising parameters for criteria.
- 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
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.
- Creating Financial Reports Using Access
- Exercise 1: Creating and Enhancing an Access Report
- Using Access PivotTables and Pivot Charts
- Exercise 2: Displaying Data Using a PivotTable View
- Exercise 3: Displaying Data Using a PivotChart View
- 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.
- 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
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.
- Automating Microsoft Excel from Access
- Exercise 1: Analysing 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