Building Solutions Using Excel and Access 2016 Together

Discover how to use Excel and Access 2016 together. Learn how to work with imports and exports, automate using macros and the AutoExec, build forms and reports, and more.

By combining the power of Microsoft Excel and Access 2016, you can build better end-user solutions and greatly enhance your own personal productivity. In this course, learn how to leverage Excel and Access, and get the most out of both tools working together. Robin Hunt shares strategies and techniques that can help you work smarter with these two programs, and avoid needing to manually rebuild each time you run reports by building solutions. 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 explains how to work with imports and exports, automate using macros and the AutoExec, build forms and reports, and more.

Introduction

• Welcome
• Using the exercise files
• What you should know

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

• How Excel and Access can work together
• Using the Problem Steps Recorder
• Using Microsoft Office screenshots

2. Building Tables in Excel and Access

• Creating Access database tables
• Import and link Excel data as tables
• Creating tables from named ranges
• Creating basic queries using tables
• Maintaining linked tables

3. Calculating Data in Excel and Access

• Calculating in Excel versus Access
• Mining data in Excel: Common formulas
• Calculating in Access 2016 tables
• Calculating in Access 2016 queries

4. Building Forms for Your Solution

• Building a basic form on Excel tables
• Adding command buttons for navigation
• Setting the Access database options

5. Building Reports for Your Solution

• Building parameter queries using forms
• Setting drop downs for parameters
• Building reports on your new queries
• Refining your navigation forms
• Exporting information back to Excel

6. Building Macros to Run Imports and Queries

• Building an import-export macro
• Action queries in Access 2016
• Building macros to run on-demand queries
• Using the AutoExec macro in Access
• Building macros on events and buttons

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

• Building queries to create new datasets
• Building forms to filter your data
• Creating macros that export data

Conclusion

• Next steps