Building Solutions Using Excel and Access 2019 Together

Course details

By combining the powers of Microsoft Excel and Access 2019, you can automate repetitive Excel tasks and boost your personal productivity. In this course, learn strategies and techniques that can help you work smarter with Excel and Access, and avoid needing to manually rebuild each time you run reports by building solutions. Discover how to most effectively leverage these two programs, and get the most out of both tools working together. Robin kicks off the course by reviewing how Excel and Access can be used together, and explaining how to build tables and calculate data in these two programs. She also demonstrates how to work with imports and exports, automate using macros and the AutoExec, build forms and reports, and more.

Learning objectives
How Excel and Access can work together
Using the Problem Steps Recorder
Leveraging screen capture tools
Building tables in Excel and Access
Creating basic queries using tables
Maintaining linked tables
Calculating in Excel versus Access
Building forms
Building macros to run on-demand queries
Generating datasets using parameters, queries, and macros

Concepts

0. Introduction

01 - Increase productivity by leveraging Excel and Access together
02 - What you should know
03 - Exercise files

1. Understanding How to Use Excel and Access for a Solution

04 - How Excel and Access can work together
05 - Use the Problem Steps Recorder
06 - Use Microsoft Office screenshots

2. Building Tables in Excel and Access

07 - Create Access database tables
08 - Import and link Excel data as tables
09 - Create Access tables from Excel data
10 - Create basic queries using tables
11 - Work with the Linked Table Manager

3. Calculating Data in Excel and Access

12 - Calculate in Excel versus Access
13 - Common formulas for data mining in Excel
14 - Calculate in Access tables
15 - Calculate totals in Access queries

4. Building Macros to Run Imports and Queries

16 - Save imports and exports
17 - Action queries in Access
18 - Build macros to run on-demand queries

5. Building Reports for Your Solution

19 - Build a basic report
20 - Build parameter queries
21 - Build reports on your new queries
22 - Export information back to Excel

6. Building Forms for Your Solution

23 - Build forms and navigation forms
24 - Add reports to your navigation form
25 - Build forms for parameter queries
26 - Add command buttons for navigation
27 - Build macros on events

7. Using Parameters, Queries, and Macros to Generate Datasets

28 - Build data links to Excel
29 - Create macros that export data
30 - Use the AutoExec macro in Access
31 - Set the Access database options
32 - Finalize and run the solution

Conclusion

33 - Next steps