Learn the Basics of the SQL Programming Language
Welcome
This seminar will teach you the basics of the SQL programming language. You will learn how to construct SELECT queries, filter and sort your results, and use basic SQL commands and operators.
This seminar was developed to teach you how to use the SQL language to enhance your Access databases. As I create more and more advanced Access seminars, I see that it's VITAL for my students to have a firm grasp of the SQL language. Knowing just a little bit of SQL can help you tremendously. You can use SQL to enhance your forms and reports. You can create dynamic SQL statements to change list and combo box data on the fly. Plus, you can reduce the clutter in your database by getting rid of "saved" queries.
Resources
Main Seminar Goals
- Learn about SQL and what it is
- Discover why you should learn to program in SQL
- Create basic SQL SELECT statements
- Learn the FROM, WHERE, and ORDER BY clauses
- SQL operators AND, OR, and IS NULL
- The LIKE command and wildcard characters
- Learn IN, DISTINCT, DISTINCTROW
- Build a list box with a dynamic SQL sort
- Create a form with a dynamic SQL RowSource
Topics Covered
We will begin by learning about SQL: what it is, what it means, and why you should learn it. You'll see some examples of basic SQL statements.
Next you will learn how to construct a basic SQL SELECT statement. You will see how Access has been generating SQL statements for you all along when you use the "Design View" query builder. You'll learn about the FROM, WHERE, and ORDER BY clauses.
Next we'll dive deeper into WHERE clauses, which is where the power of an SQL statement is. You'll learn about the different operators, how to work with string values, TRUE/FALSE values, and dates. You'll see how the BETWEEN keyword works. You will learn how to use AND, OR, IS NULL, and IS NOT NULL.
You will learn how to set up Parameter Queries, use the LIKE keyword and wildcard characters to find partial matches in your text values. You'll learn how to search for ranges of characters and values. You'll learn about the IN keyword.
You will learn more about the ORDER BY clause, including ordering query results based on multiple fields and in ascending and descending order. You will learn the difference between the DISTINCT and DISTINCTROW keywords, and how to use them to return a list of unique values from your tables.
Next we'll build two forms to demonstrate practical examples of how SQL is useful. First, we'll create an unbound form with a listbox showing our customers. We'll create two buttons on the form that will allow us to change the sort method of the listbox by either the customer's last name or his credit limit. This will all happen in VBA code with an SQL statement. No queries will have to be built.
Next, we'll use a similar technique to allow one form to display data from multiple tables. We'll create a form showing a list of first and last names. Using buttons and custom SQL statements, we can change the table that provides data to this form. We can show either customers, or employees, or we can use a special UNION query to show both at the same time. This is an example of how to dynamically modify the RecordSource property of a form at runtime using SQL.
Again, this seminar is the perfect first step for anyone who wants to learn how to enhance their Microsoft Access databases with the added power of custom SQL. It is the first part of a three-part series. Part 2 will cover using SQL to modify and manipulate data (add, edit, delete records). Part 3 will teach you how to modify the design of your database (table and query structure) using SQL.
Of course, if you have any questions about whether or not this seminar is for you, please contact me.
Pre-Requisites
This is a Developer-Level Seminar. There will be some VBA coding. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video.
Version
I am using Access 2010 in this seminar, however the lessons are perfectly valid for all versions of Access from 2003 and later. It's currently 2022 and I just recently verified that everything in this seminar still works with Access 2019 and Office 365. The SQL programming language as far as it pertains to Microsoft Access has not changed in a very long time.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access sql, structured query language, select, from, where, order by, between, and, or, in, is null, is not null, parameter, query, queries, like, wildcard, asc, desc, distinct, distinctrow
