ONLINE - Using Access & Excel to Analyze Data - 4/12-13/2021
14 hours (2 days)
This class is approximately 5 hours of Access and 9 hours of Excel. This class will not teach you how to build a database. In Access, the focus is understanding database tables, relationships and basic queries. The focus in Excel is all the powerful tools available to help manage data.
8:30 a.m. – 4:30 p.m. Central Time
One-hour break for lunch and a 15-minute morning and afternoon break.
Method of Delivery
This class will be held online using GoToMeeting. Class participants will receive a PDF of the book and will be able to access the class files online.
When you register, be sure to use the email that you would like your GoToMeeting invitation sent to. The meeting invitation will be sent no later than 1 hour prior to the class. Check your junk mail if you do not receive an invitation, especially if you have a Gmail or Hotmail account. You do not have to have a GoToMeeting subscription to participate.
The web browser client will download automatically when you start or join your first GoToMeeting meeting and is also available for manual download here.
It is a good idea to download this prior to the online meeting.
In my years of working with data, I use both Access and Excel. Excel has powerful tools like PivotTables, Power Query and Power Pivot. However, due to Excel’s record limitations, Access can also be a great data tool. Understanding database field types, how to write queries in Access and how to create basic Access calculations is not only great when you are using Access, but it will help you also better understand calculations in Excel Tables and Power Pivot. This class is designed for the beginner Access user who wants to learn Access. The Excel portion of the class is for the intermediate Excel user. In this class we will learn about relational databases. We will look at how to create and relate tables, build Access queries and learn how to Manage Data in Excel with Tables and PivotTables. We will also take a brief look at how to work with Power Query and Power Pivot. And, finally, we will learn to export and import data from Access to Excel.
Introduction to Relational Databases
- Understand the difference in Relational Databases vs Flat Databases
- Look at the Access Objects – Tables, Queries, Forms & Reports
- Understand Data Types and Field Properties in Access
- Learn how to build an Access table
- Learn to relate tables in Access
- Understand the purpose of a Primary Key
- Create a Basic Form
- Create and Save a Basic Query
- Navigate the Query Views
- Work with the Parts of the Design Grid
- Use Comparison and Wildcard Operators
- Work with AND and OR Conditions
- Learn how to Perform Calculations in Query
- Use the Expression Builder
- Work with Parameter Queries
- Find Duplicates using a Query in Access
- Learn How to Remove Duplicates in Access
- Learn How to Identify Duplicates in Excel using Conditional Formatting
- Remove Duplicates in Excel.
Importing and Exporting
- Import and export from Access to Excel, Access, CSV and other Sources
- Connect Excel to an Access database
Creating Tables and PivotTables
- Review Best Practices for Arranging Data for use with Tables or PivotTables
- Understand the difference between Filtering data and using Tables
- Learn the power of Tables
- Learn PivotTable Basics
- Learn to Refresh and Modify PivotTables
- Work with Slicers and Understand How Slicers can help with Dashboards
- Understand PivotTable Cache
- Work with PivotTable Timelines
Advanced PivotTable Topics
- Creating Calculated Fields
- Creating Calculated Items
- Office 365 New Data Options
- Changing the Design and Layout of your Pivot Table
Introduction to the Power Pivot
- Understand What Power Pivot is and how it is different than a regular PivotTable
- Import Excel Tables into the Data Model
- Learn How to Link Tables in the Data Model
- Worth with the Related() Function
- Learn Basic Calculations in the Data Model
- Creating a PivotTable using Multiple Data Sheets
Power Query a.k.a. Get and Transform
- Understand Power Query and where is it located in 2013, 2016 and Office 365
- Review Types of Data Connections and Power Query Editor Window
- Review and Change Data Types
- Load to the Data Model
- Learn Data Specific Editing Tools such as Text, Numbers, and Date Tools
- Work with Filling Data Up and Down
- Understand How to Split and Combine Columns of Data
- Learn How to Add Conditional Columns
- UnPivot Data
- Merge Data and Work with Joins
- Appending Multiple Data Sets
Who Should Attend?
Intermediate Excel users and Beginning to Intermediate Access users who would like to expand their knowledge of Microsoft Access and Excel.
Understand how to use excel and build basic formulas like Sum and Average. Also know the basics of sorting and filtering in Excel. No prior knowledge of Access is needed.
The cancellation deadline is 5 business days prior to the class. If you are unable to attend, you may send a substitute. If you are a "no show" or you do not send a substitute you will be charged full price for the class.
Cloud Training Services will consider emergency situations such as a death in the family, illness, and weather, and will make every attempt to work with the person in the event of an emergency.
|Event Date||04-12-2021 8:30 am|
|Event End Date||04-13-2021 4:30 pm|
|Cut off date||04-12-2021|