Beyond The Spreadsheet Managing Financial Information Using Microsoft Office Access 2003
Corso
A Torino
Hai bisogno di un coach per la formazione?
Ti aiuterà a confrontare vari corsi e trovare l'offerta formativa più conveniente.
Descrizione
-
Tipologia
Corso
-
Luogo
Torino
Obiettivo del corso: Introduction 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 i. Rivolto a: 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.
Sedi e date
Luogo
Inizio del corso
Inizio del corso
Profilo del corso
Before attending this course, students must have:
· A basic understanding of relational databases.
· An understanding of
Access tables, queri
Opinioni
Materie
- Reporting
- Management
- Visual basic
- Office
- MAN
Programma
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, organizing, and reporting vital business information and demonstrate the power of Access as a financial data management and analysis tool.
Audience
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.
At Course
Completion
After completing this 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.
Prerequisites
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 Excel and Microsoft
Word).
Course Outline
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.
About the
Author
F. Scott Barker, a Microsoft Access MVP, is the author of numerous books on Microsoft Access, most notably F. Scott Barker's 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
Hai bisogno di un coach per la formazione?
Ti aiuterà a confrontare vari corsi e trovare l'offerta formativa più conveniente.
Beyond The Spreadsheet Managing Financial Information Using Microsoft Office Access 2003