Building Solutions Using Excel and Access 2019 Together

Learn strategies and techniques that can help you work smarter with Excel and Access 2019, and avoid needing to manually rebuild each time you run reports by building solutions.

Introduction

• Increase productivity by leveraging Excel and Access together
• What you should know
• Exercise files

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

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

2. Building Tables in Excel and Access

• Create Access database tables
• Import and link Excel data as tables
• Create Access tables from Excel data
• Create basic queries using tables
• Work with the Linked Table Manager

3. Calculating Data in Excel and Access

• Calculate in Excel versus Access
• Common formulas for data mining in Excel
• Calculate in Access tables
• Calculate totals in Access queries

4. Building Macros to Run Imports and Queries

• Save imports and exports
• Action queries in Access
• Build macros to run on-demand queries

5. Building Reports for Your Solution

• Build a basic report
• Build parameter queries
• Build reports on your new queries
• Export information back to Excel

6. Building Forms for Your Solution

• Build forms and navigation forms
• Add reports to your navigation form
• Build forms for parameter queries
• Add command buttons for navigation
• Build macros on events

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

• Build data links to Excel
• Create macros that export data
• Use the AutoExec macro in Access
• Set the Access database options
• Finalize and run the solution

Conclusion

• Next steps