SQL for Microsoft Access


arrow-left
Back To All Courses

SQL for Microsoft Access

44

Lessons

All

Skill Level

4:27h

course Duration

English

Audio

Eng

Subtitles

Share This Class:

Overview

Do you want to learn how to write more advanced queries in Access. And do you want your queries to be portable into SQL Server and Access?

Hopefully, by the end of this course, you will also feel “quite solid” in Access SQL, and be able to use the extra functionality allowed only when using Access SQL to build even better databases.

Completing this course will help you:

  • Know all 6 clauses of the SELECT statement, and in which order.
  • Add functions and formulas into your code.
  • Do basic and advanced JOINs, some of which cannot be done in the graphical interface.
  • Create UNION queries, which also cannot be done in the graphical interface.

Who is the course for?

This course is for you if you want to develop your Microsoft Access skills by learning how to code SQL queries.

This course is for you if you want to create more advanced queries in Access.

This course will use the latest version of Access, but is ideal for you if you use any modern version – Access 2007, 2010, 2013, 2016 or 365.

This course is also for you if you use an older version of Microsoft Access, but want to learn the newer versions.

Learning Path

Let’s start with SQL

  • A basic SELECT statement, using the SELECT and FROM clauses
  • Adding a WHERE clause
  • Summarising data with a GROUP BY clause
  • Adding a HAVING clause
  • Adding some order – using the ORDER BY clause

Adding Functions and Formulas

  • GROUP BY – Adding Aggregating functions
  • Using Aliases
  • Using WHERE on Text fields
  • Using WHERE on NULLs
  • Using WHERE on Date fields
  • Date/Time Functions
  • Maths and Text Functions
  • Conditional functions

Adding additional tables

  • Where can you use SQL statements?
  • Adding a second table
  • Different types of JOINs
  • Writing a JOIN query
  • Inequality Joins
  • Adding a third table
  • Do I need complicated JOINs? Subforms and subreports

 
Find missing data, and delete and update data

  • Missing data
  • Creating a Make Table query
  • Inserting data
  • Deleting data
  • Updating data

 
More advanced queries

  • Importing or Linking data – Tables
  • Creating a Pass-Through query

 
Importing Data from SQL Server
We’ll will now be creating sub-queries, working through objectives 7b-e of the exam 70-461. We’ll be created correlated subqueries, where the results of the subquery depend on the main query. We’ll be looking at Common Table Expressions using the WITH statement, and we’ll be using what we have learned to solve a common business problem.

We’ll be looking at functions (objective 14), including the three different types of User Defined Functions (UDF): scalar functions, inline table functions, and multi-statement table functions. We’ll then complete objective 6 by looking at synonyms and dynamic SQL, and objective 8 by looking at the use of GUIDs. We’ll also look at sequences.

We’ll have a look at XML. Finally, for SQL Server 2016 and later (exam 70-761), we’ll examine JSON and Temporal Tables.

What people are saying

“This is an excellent course, very well organized, structured, explained and paced. In addition, the course is content rich, provides a very good context and helpful tips! This is exactly what I was looking for. Thank you very much!”
Dmitry
“I am a 51 year old complete beginner and just wanted to learn access to create my own database for a side business. I first took an MS Access course and then this SQL for Access course which allowed me to understand access better and to add some useful functionality to my database. Really enjoyed it, felt like a great introduction to SQL.”
Mark
“I am very satisfied with what I’ve learned in this course. I’ve been using access for about 2 years and the things that I have learned will go a long way to making my and my co-workers job so much easier.”
Felton
“The instructor makes easy what it looks difficult to understand. I like the way of explaining how to write the SQL code. The teacher sometimes writes SQL that doesn’t work but these errors are not forced. He explained what it is wrong and then he drives you to the correct SQL code very naturally.”
Carlos
Previous
Next

Are You Ready To Start?


arrow-icon-size3
Get Started

More Courses

You might also be interested in these courses

98-364

Database Fundamentals in 8 hours

Learning Database Fundamentals with Microsoft SQL Server

Database Fundamentals in 8 hours

View Course

SSRS

Microsoft SQL Server Reporting Services

Learn to create reports in SSRS

SQL Server Reporting Services

Learn how to create reports in SSRS
View Course