Excel
and Access skills are used everyday in the workplace and are a
prerequisite for many jobs. This pre-recorded video course is highly
requested and covers Microsoft Excel & Access 2007 Integration database. This course comes with two Excel e-books and two Access e-books that will be sent out after registration.
Chapter 0: Installing Access 2007 onto your PC
Chapter 1: Getting Excel Data into Access
Differences Between Access and Excel
The Access Table
The Table in the Design View
Bringing Your Excel Data into Access
Importing a Worksheet into a New Table
Linking an Excel Worksheet to Access
Appending an Excel Worksheet to an Existing Table
Chapter 2: Analyzing Excel Data with Access Queries
Introduction to Access Queries
Sorting Query Results
Filtering Query Results
Querying Multiple Tables
Understanding the Concept of Relational Databases
Creating a Query that Joins Two Tables
Using Aggregate Queries
Group By, Sum, Avg, Count, Max, Min
Using Calculations in Your Analysis
Leveraging Access Query Wizards to Solve Common
Excel Problems
The Find Duplicates Query Wizard
The Find UnMatched Query Wizard
Crosstab Queries
Chapter 3: Sprucing Up Excel Data with Access Reports
A Closer Look at the Access Report
Creating Your First Report
Viewing Your Report
Report View
Layout View
Design View
Page Footer
Report Footer
Creating and Modifying Grouped Reports
Grouping
Sorting and Totaling
Customizing Reports with Formatting
Page Layout Techniques
Solving Page Break Issues
Multi-Column Report Layout Example
Formatting Techniques
Formatting to Make Reports More Readable
Formatting to Highlight Specific Results
Chapter 4: Using PivotTables and PivotCharts in Access
Working with PivotTables in Access
The Totals and Detail Area
The Row Area
The Column Area
The Filter Area
Creating a Basic PivotTable
Creating an Advanced PivotTable with Details
Saving Your PivotTable
PivotTable Options
Expanding and Collapsing Fields
Changing Field Captions
Sorting Data
Grouping Data
Using Date Groupings
Filtering for Top and Bottom Records
Adding a Calculated Total
Working with PivotCharts in Access
The Data Area
The Series Area
The Category Area
The Filter Area
Chapter 5: Getting Access Data into Excel
Exporting the Data from Access
Using the Get External Data Menu
Using the Microsoft Query Wizard
Going Beyond the Wizard in Microsoft Query
Chapter 6: Leveraging Macros in Excel and Access
What Is a Macro?
Why Use a Macro?
Creating Macros in Microsoft Excel
Creating Macros in Microsoft Access
Introducing Excel Macros
Using the Macro Recorder
The Macro Recorder User Interface
Recording Macros with Absolute References
Recording Macros with Relative References
Excel Macro Examples
Macros in Microsoft Access
Creating your First Access Macro
The Macro Design Template
Common Actions in Access Macros
Access Macro Example
Chapter 7: VBA Fundamentals
What is VBA?
The Visual Basic Editor or VBE
Variables
Procedures and Functions
VBA Coding Funfamentals
Examples of Using VBA
Chapter 8: Using VBA to Move Data Between Excel and Access
Understanding ADO Fundamentals
The Connection String
Declaring a Recordset
Return Read Only Data from a Table or Query
Return Updateable Data from a Table or Query
Writing Your First ADO Procedure
Referencing the ADO Object Library
Writing the Code
Using the Code
Understanding SQL Fundamentals
Basic SQL Syntax to Select Data
Writing Your First ADO/SQL Data Extract
Using Criteria in Your SQL Statements
Common Scenarios Where VBA Can Help
Query Data from an Excel Workbook
Append Records to an Existing Excel Table
Append Excel Records to an Existing Access Table
Querying Text Files
