ExcelCEO Access® 2016 and SQL Training Course

Benefits of Learning Access with ExcelCEO

Getting the right data is by far the hardest part of our jobs. Once you get the right data, slicing and dicing it in Excel, Access, or even putting it on the web is the easy part. For financial people, an in-depth understanding of how to work with databases is essential, as we work with financial data.

ExcelCEO Access 2016 and SQL contains Beginning, Intermediate, and Advanced training on Tables, Queries, Forms, Reports, External Data Sources, and SQL. With detailed, step-by-step instructions and hundreds of screenshots to guide in hands-on training, you will gain skills through interaction with the Access 2013 program and develop master-level skills through first-hand experience! Read on to see details of the specific training you would receive after registering for ExcelCEO Access 2016 and SQL.

What is Included with ExcelCEO Access 2016 and SQL Registration

Once you register for the Access 2016 and SQL complete self-study course, you would gain access to the following:

• (1) ExcelCEO student profile** at ExcelCEO.com using the email address you register with, and the temporary password assigned (once you are registered and logged in, you would be able to change this at any time)
    ** Only one student profile per registration. Training materials should not be shared with anyone who has not registered for the specific ExcelCEO training.
    If you have more team members who want to take ExcelCEO training, ask about group discounts!
• (1) Access 2016 and SQL complete self-study course PDF manual (download) containing easy-to-follow, step-by-step instruction text and hundreds of screenshot aides throughout. **No audio-/video-based training aides are available at this time
• Complete course database files download for hands-on training (download)
• Review Questions checkpoints within each of the 15 chapters of training to provide additional training and exposure to the Access 2016 user interface, including the Office Ribbon, menu option placement, and more. These sections are not graded, and provide tip explanations for each answer selected.
• Chapter exams (one per chapter) that are designed to test the accuracy with which you have completed the chapter projects and understood the training provided. Chapter exams are graded, and explanations are not provided along the way. Chapter exams must be completed with a minimum of 70% to move on to the next chapter. You can review missed questions once you pass the specific exam
• Optional Supervisor profile for tracking progress
• Certificates tracking for CPE credit - this course qualifies for up to 40 hours of NASBA-certified CPE/QAS credit, awarded by chapter. If taking the course for CPE credit, NASBA policy is that training you plan to submit for CPE credit must be completed within one year from the date of purchase. ExcelCEO does not place time restrictions on access to ExcelCEO students profiles for designated, registered ExcelCEO students, so whether you are taking the course for personal development, or even if you take the course for CPE credit, and the one year expires, or if you graduate from the training, you would still have access to your ExcelCEO student profile as long as you remained a registered ExcelCEO student.
• Upon graduation, you would qualify to be listed in the Graduate Verification at ExcelCEO.com as evidence of your Reporting and Analysis mastery! You would also receive a high-quality, printed certificate to frame for your office, desk, or wall.

Course Overview — Tables

Begin with the basics of Access, starting with Tables. With ExcelCEO Access 2016 and SQL:

• Understand how to open, filter, sort, and design a table.
• Recognize Field Properties
• Identify which field to set as a table Primary Key
• Add a record to a table

Queries

Once you understand the basics of tables, the next step to making use of them through use of virtual tables, a.k.a. Queries.

• Create a simple query beginning with Query Wizard
• Use Query Design to setup a virtual table.
• Setup criteria to filter your query, delete fields from your query design grid
• Learn the basics of formulas within the Query Design Grid
• Join tables, format fields, name aliases, and establish relationships between table fields
• Understand the benefits of Parameter Queries, subqueries, Crosstab, Action, Make Table, Append, Update, Data Definition, and Delete queries
• Create macros
• Audit queries for accuracy

Forms

After you have a foundation in Queries, you will learn to make the data presentable with Forms.

• Recognize the structure of a Bound form
• Add queried records into a form
• Make the form presentable using form Headers, Detail sections, and Footers
• Fine-tune your report appearance with form Rulers, Snap to Grid, and Toolbars
• Use Form Design View to dress-up your form with Icons, graphics, and labels

Intermediate Forms is designed to expand your knowledge of form-building by teaching you to skills with Unbound forms, Command Buttons, creating Custom Categories, Subforms, custom Startup functionality, and Special Features

Reports

After Intermediate Forms, your training transitions to Reports. Included in the Reporting chapters is a chapter dedicated to connecting to hosted (or external) databases using a Data Source Name (DSN). At the end of the Access course, you will create an interactive database that produces a financial statement that can be run for any level of the organization, with the base data being tied to a hosted SQL Server database.

• Learn the basics of building a report using Report Labels and Text Boxes
• Establish Object Properties
• Identify how to add Grouping and Sorting sections
• Enhance your reported data with formulas that display Subtotals
• Align objects within your report in Report Design View

In Intermediate Reporting, learn to edit and copy existing reports, add new Header and Footer sections, resize and reorient your report, calculate subtotals and percentages, and create report-based mailing labels

External Data Sources

Connecting to a database stored on your local computer is useful, but Access is capable of so much more when you learn to setup a Data Source Name (DSN) and connect to External Data Sources.

• Learn the function of external data sources and how to connect to them
• Create a DSN
• Link to external data sources and import objects

Advanced Reporting ties your Access skills together

• Sort on multiple levels within your report
• Build a summary report
• Create layout grouping
• Tie report variables to a form
• Recognize the differences and benefits and uses for hard-coded and query-based combo boxes
• Expand the summary statement into a Detail statement
• Utilize database utilities
• Analyze Performance of your database
• Create a detailed statement about your database using Documenter

Introduction to Structured Query Language (SQL)

After the Advanced Reporting sections of this course, take a break to learn the basics of SQL, and how this database querying language ties into Access, as well as how you can use SQL View to write SQL strings directly!

• Create a basic SQL View using SELECT and FROM
• Recognize how to use ORDER BY and WHERE clauses
• Select AND and OR operators, and distinguish the uses for them
• Enhance data searches using wildcard characters
• Setup Calculated Fields and field aliases
• Return unique records with SELECT DISTINCT
• Establish relationships between tables using correct Joins
• Combine data from multiple related tables using Union queries

Access 2016 and SQL Capstone

With a solid foundation of Access and SQL, put your reporting and analysis skills to the test with the Access 2016 and SQL course capstone, the Access Master project. In this project, you will combine your skills from Excel, Access, and SQL to create a repeatable analysis, and qualify to be called an ExcelCEO Reporting and Analysis Master.