Access SQL Seminar Part 1
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
Intro In this video, you will learn the basics of the SQL programming language as it applies to Microsoft Access, including what SQL is, why it is useful, and how to use core components such as Select, From, Where, and Order By clauses. We will cover different operators, the Between keyword, wildcard characters, and dynamic SQL, plus build practical examples like a customer list form with data sorting options and a reusable names form using union queries. This session assumes you already know how to work with tables, queries, forms, and reports in Access. This is part 1.Transcript Welcome to the Access SQL Seminar Part 1, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. The purpose of this seminar is to teach you the SQL programming language to enhance your Microsoft Access databases.
You will learn how to add more power to your database forms and reports, while at the same time reducing the clutter of having lots of extra saved queries in your database. Plus, you will learn some new query features that are only available in Access if you know SQL, such as a Union Query.
In today's class, you will learn what SQL is, what it stands for, and why you should know SQL in order to improve your Access databases. You will learn all about the different types of SQL components, including the Select, From, Where, and Order By clauses. You will also learn about all the different types of operators, the Between keyword, Wildcard characters, and more.
After we have gone through all the theory of SQL and some examples of simple queries, we will build two practical examples in Access. The first will be a customer list form that will show customer details, and we will change the list by clicking on two buttons. We will sort by last name, or sort by their credit limit, and we will use dynamic SQL statements to change the data.
For our second practical example, I will show you how you can reuse forms and reports by taking one form and showing either customers, employees, or both by using an SQL statement to change the data that fills in the form.
This seminar covers the SQL programming language. The examples that we will cover in today's class are specific to Microsoft Access, but they are generally universal for most database programs. SQL is usually SQL regardless of what database program you are using. There are some minor differences, and I will mention what some of them are, but usually if you know SQL, it is applicable to all database programs.
This is not a class on Microsoft SQL Server. SQL Server is a specific type of database server made by Microsoft, and that will be covered in a separate class. Again, this class is only on the SQL language, not Microsoft SQL Server. Although the SQL language that you will learn in today's class will be applicable if you decide to use SQL Server in the future.
For large corporate databases, web servers, or databases that get a lot of traffic or need a lot of security, I do recommend Microsoft SQL Server as your database backend, and I will have separate courses for that in the future. This course is for the SQL language.
Prerequisites: This is not a beginner Access class. I am not going to teach you how to make tables, basic queries, forms, and reports. I assume you know how to do that. If not, I recommend my Access 100 classes, where you can learn the very basics. The 200 series covers more intermediate subjects, such as advanced queries, and things of that nature, which is recommended. My Access 300 series covers VBA programming design and the VBA language, which will be helpful, although not required.
We are going to be doing a little bit of programming when we get to the last two lessons, when I show you these sample forms. I will walk you through it step by step, so if you have never done any programming before, it should not be too hard to follow. Again, the focus of today's class is on SQL, but in order to put some of that SQL into use, I am going to show you a little bit of programming. Follow along with me and you should not have any problems.
I should also note that I am in the process of renaming my courses as I rerecord them for Access 2010. The 100 series is going to be called Beginner, the 200 series Expert, and the 300 series Advanced. If you go to the website and cannot find 101 or 201, that is why - the new courses will be called Beginner, Expert, and Advanced. Do not get confused if you see them like that on the website.
As far as the version used, I will be using Access 2010 for this class. If you are using 2007, you should not have any problems following along. If you are using 2003 or before, all the material is pretty much exactly the same. The SQL has not changed, the VBA code has not changed, just the menus have changed - where some of the things are located. Access 2007 added the new ribbon. If you are familiar with database design, making tables, queries, forms, and such, you should not have any problems following along. There are a couple of different buttons, and I will show you where they have moved to in Access 2003.
Now let's take a minute and learn exactly what is covered in each lesson of the seminar.
In lesson 1, we are going to learn what SQL is and why it is important to learn.
In lesson 2, we will look at the components that make up a basic SQL command, including the Select, From, Where, and Order By clauses.
In lesson 3, we are going to begin looking at more complicated Where clauses.
In lesson 4, we will continue on with Where clauses. We will learn about parameter queries, the Like keyword, Wildcard characters, the In clause, and ranges of characters.
In lesson 5, we are going to take a look at the Order By and Distinct clauses.
In lesson 6, we are going to tie everything that we have learned together. We are going to make a customer list form, which is an unbound form with a list box on it that has customer information. We will make two buttons that will change the way the data is sorted, either by last name or by the customer's credit limit, and we will use dynamic SQL and a little bit of VBA code to do that.
In lesson 7, I am going to show you one more good example of where dynamic SQL comes in handy. We are going to make a name list form, which is a continuous form with a list of names on it. We will make buttons to change the SQL in the form, so you can see either customers or employees, or both of them at the same time using a union query.
This seminar is part 1 of a three-part series. Today's seminar is going to cover the basics of SQL and teach you about Select queries, which involve reading information from tables.
Part 2 gets a little more advanced. We will learn about action queries, which means using SQL to manipulate data. It is basically the same thing as an append query or delete query in normal Access. We will also learn how to perform joins in SQL, and learn about different functions like Sum, Max, Min, and the different aggregate functions.
Part 3 is all about using SQL to modify the structure of your database, which is actually modifying and manipulating table and query design. That is a lot more advanced. For more information on what these other parts of the seminar cover, you can find complete outlines on my website.
If you have questions about anything covered in today's class, please feel free to post them in the Access Forum. We have a very active community on the website, with myself and lots of different students who like to post questions and answers to help you out if you have any problems with Access. We have a general Access forum on the website. You will find a big link that says Forums on the menu bar.
For this specific class, you will see in the video player or online in our theater a little window that pops up next to the videos, and that is the student forum as well. You can post questions and answers there, and we will do our best to help you out.
If you have any other kind of questions, you can contact us directly at accesslearningzone.com/contact.
Now sit back, relax, and get ready to learn some Access SQL.Quiz Q1. What is the main focus of the Access SQL Seminar Part 1?
A. Teaching the SQL programming language for Microsoft Access databases
B. Learning how to design tables in Access
C. Understanding database security options in SQL Server
D. Advanced VBA programming for Access
Q2. Which of the following is a benefit mentioned of learning SQL in Access?
A. It allows you to avoid using forms and reports
B. It reduces the number of saved queries needed
C. It restricts your use of query features
D. It eliminates the use of macros
Q3. What is a unique SQL query type available in Access only if you know SQL?
A. Find Query
B. Crosstab Query
C. Union Query
D. Split Query
Q4. Which clauses are covered as part of SQL components in this seminar?
A. Merge, Append, Delete
B. Select, From, Where, Order By
C. Insert, Update, Remove
D. Group, Transform, Split
Q5. What SQL feature will you learn about concerning handling groups of characters?
A. Conversion functions
B. Wildcard characters
C. Transaction logs
D. Primary keys
Q6. What does the first practical example in Access involve?
A. Creating a new database from scratch
B. Making a customer list form with dynamic SQL sorting
C. Designing a relational database schema
D. Writing macros for report automation
Q7. In the second practical example, what will be demonstrated?
A. Using SQL to back up a database
B. Reusing forms and reports with dynamic SQL
C. Encrypting SQL statements for security
D. Importing data from Excel using SQL
Q8. Is this seminar a class on Microsoft SQL Server?
A. Yes, it is focused specifically on SQL Server
B. No, it only teaches the SQL language, not SQL Server itself
C. Yes, and includes database administration tasks
D. No, it covers only VBA programming
Q9. What level of Microsoft Access knowledge is assumed for students in this seminar?
A. Complete beginners with no prior Access experience
B. Intermediate to advanced users who know forms, queries, and reports
C. Only experts in VBA programming
D. Users interested only in SQL Server
Q10. Which Access versions are explicitly mentioned as being suitable for following along?
A. Access 2016 and newer only
B. Access 2010 and Access 2007
C. Only Access 2003 and prior
D. Access 95 and earlier
Q11. Which SQL topics will be covered in the lessons for this seminar?
A. Data warehousing and SQL Server integration
B. Select queries, Where clauses, Order By, Distinct, and basic operators
C. Advanced networking and server configuration
D. Creating pivot tables and dashboards
Q12. What will Part 2 of the SQL seminar series cover?
A. Performing action queries, joins, and aggregate functions
B. Creating custom VBA controls
C. Web-based Access database publishing
D. User interface design
Q13. If you have questions about the content, where can you seek help?
A. Only through official written documentation
B. The AccessLearningZone online forum and student forum
C. Only via email to Microsoft support
D. Local Access training centers only
Q14. What is the main purpose of learning dynamic SQL in this seminar?
A. To build more interactive and flexible forms
B. To create database backups
C. To perform batch updates
D. To automate report exports
Q15. Which is NOT a prerequisite for taking this seminar?
A. Knowing how to create basic forms and reports in Access
B. Completion of Access 100 series classes if you are a beginner
C. Advanced experience with SQL Server administration
D. Familiarity with Access 2007 or 2010 recommended
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B; 14-A; 15-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers the first part of the Access SQL Seminar. I'm Richard Rost, your instructor, and my goal is to teach you how to harness the power of SQL to take your Microsoft Access databases to a higher level.
By learning SQL, you will find it easier to make forms and reports more efficient by minimizing the number of saved queries you need in your database. You'll also gain access to features in Access that require knowledge of SQL, like Union Queries, which are not available through the standard query designer.
In this seminar, I introduce what SQL is, explain its full name, and discuss why it's a crucial skill if you want to make your databases more efficient and flexible. You'll learn about essential SQL components, specifically the Select, From, Where, and Order By clauses. We'll also talk through the different types of operators used in SQL, how to use the Between keyword, work with wildcard characters, and much more.
After we thoroughly cover the foundational theory and look at some straightforward query examples, we'll move on to some practical projects in Access. First, you'll see how to build a customer list form to display customer information dynamically. I'll show you how to sort that list by last name or by credit limit just by clicking different buttons, putting dynamic SQL statements to work.
Next, I'll explain how to make your forms and reports more versatile by allowing one form to display customers, employees, or even both, all based on changing the SQL statement that supplies the data.
This seminar is focused on SQL as a language, not on Microsoft SQL Server, which is a specific server software from Microsoft. While this class uses Access for all examples, most of what you'll learn today applies to other database programs as well, since SQL syntax is quite universal. However, if your needs involve larger or more complex databases, or if you plan to use web servers or require high-security solutions, I would recommend Microsoft SQL Server as your backend, and I have separate courses to cover that in detail.
If you're new to Access, please note this course is not designed for beginners. I won't be explaining how to make tables, basic queries, forms, or reports. Those topics are covered thoroughly in my Access 100-level classes, which I recommend if you haven't learned those basics. For broader experience, the 200-level classes cover more advanced queries, and the 300-level series gets into VBA programming and design, which is useful though not absolutely required here.
We will do a bit of simple programming in this seminar, mainly in the last two sessions where I demonstrate dynamic forms using SQL. If you have never programmed before, I'll walk you through each step and make it as approachable as possible.
You might also notice that I'm renaming my courses as I update them for Access 2010. The classes are gradually being retitled: 100-level courses are now 'Beginner,' the 200-level courses are 'Expert,' and 300-level classes are labeled 'Advanced.' If you cannot find course numbers on the website, look for these descriptions instead.
I'm using Access 2010 during this seminar, but if you have Access 2007 there shouldn't be any issues since the changes are minimal. Even users of Access 2003 or earlier can largely follow along because the SQL language and VBA code haven't changed between these versions, only the interface has seen some adjustments.
Here's what each lesson in this seminar covers:
Lesson 1 explains what SQL is and why it's essential to learn.
Lesson 2 describes the main parts of a basic SQL command: Select, From, Where, and Order By.
Lesson 3 dives into more complex Where clauses.
Lesson 4 continues with Where, exploring parameter queries, using the Like keyword, wildcards, the In clause, and character ranges.
Lesson 5 covers the Order By and Distinct clauses.
Lesson 6 brings everything together as we build an unbound customer list form with a list box. I'll walk you through creating buttons to sort the data by either last name or credit limit using dynamic SQL and some basic VBA.
Lesson 7 provides another practical use of dynamic SQL by creating a name list form that can display customers, employees, or both at once, thanks to a union query.
This is the first of a three-part seminar series. Today, we focus on the basics of SQL and its use for Select queries, which pull table data. In Part 2, we'll look at more advanced topics, such as action queries (for updating and deleting data), joins, and aggregate functions like Sum, Max, and Min. Part 3 moves into altering your database's structure with SQL, which is an advanced topic. If you want more detail about those topics, you can find complete outlines on my website.
If you have any questions about what we've covered today, I encourage you to visit the Access Forum on my website. It's an active area where many students and I post questions and answers to help each other. For each class, there is a dedicated space to interact with other students and get help with what you're learning.
If you have other questions, you can always reach out to us directly at Access Learning Zone.
To get started, just relax and prepare to learn the essentials of Access SQL. A complete video tutorial with clear, step-by-step instructions covering everything mentioned here is available on my website at the link below.
Live long and prosper, my friends.Topic List What SQL is and its importance in Access
SQL command structure: Select, From, Where, Order By
Complex Where clauses
Parameter queries in Access SQL
Using the Like keyword in SQL
Wildcard characters in Access SQL
Using the In clause in SQL
Character range queries
Order By and Distinct clauses
Building an unbound customer list form with SQL
Sorting data dynamically with SQL and VBA
Dynamic SQL in Access forms
Showing customers or employees with form SQL
Creating and using union queries in Access |
