|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
SQL 1 Seminar Lessons Welcome to Access SQL 1. Total running time is 1 Hour, 38 Minutes. Lessons
Resources
Notes
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn this lesson, you will learn the foundations of the SQL programming language as it applies to Microsoft Access. We will talk about what SQL is, why it is important, and explore key components such as the Select, From, Where, and Order By clauses. You will learn about operators, the Between keyword, and how to use wildcards. Practical examples will show you how to use dynamic SQL to create flexible forms, including sorting customer lists and reusing forms for different data. This is part 1.TranscriptWelcome 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 and what it stands for, and you will learn 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 go 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 or employees or both, by using an SQL statement to change the data that fills 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 do decide to use SQL Server in the future. For large corporate databases, or web servers, or for databases that get a lot of traffic or need a lot of security, I do recommend Microsoft SQL Server as your database backend. Again, I will have separate courses for that in the future. This course, again, is for the SQL language. Prerequisites: This is not a beginner Access class. I am not going to teach you how to make tables and basic queries and forms and reports. I assume you know how to do that stuff. If not, I recommend my Access 100 classes, where you can learn the very, very basics. The 200 series is a little more intermediate, covering advanced queries and things of that nature. That is recommended. Of course, 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 how to do a little bit of programming. I should also note that, as of right now, 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 and 201, that is why, because 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, only the menus have changed, where some of the things are. Access 2007 added the new ribbon. If you are familiar with database design, making tables and queries and 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 is reading information from tables. Part 2 gets a little more advanced. We will learn about action queries, which is using SQL to manipulate data. That 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. You will find that we have a very active community on the website, where we have 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 in 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. QuizQ1. What is the primary goal of this Access SQL Seminar?A. To teach the basics of database design B. To introduce Microsoft SQL Server administration C. To teach the SQL language for enhancing Microsoft Access databases D. To show how to install Access 2010 Q2. Why is learning SQL valuable for Microsoft Access users? A. It replaces the need for VBA programming B. It adds query features not available through the standard Access query designer C. It is only required for very large databases D. It is mandatory for Access to function Q3. Which of the following SQL clauses will you learn about in this seminar? A. Group By, Having, and Grant B. Select, From, Where, and Order By C. Create, Drop, and Alter D. Insert, Update, and Delete Q4. What new query feature does the seminar mention that is only available through SQL in Access? A. Cross-tab Query B. Totals Query C. Union Query D. Parameter Query Q5. What types of operators and keywords will be discussed in relation to SQL? A. Arithmetic and logical only B. Only AND and OR operators C. The Between keyword and wildcard characters D. Aggregate functions exclusively Q6. The seminar's practical examples include: A. Building multi-table relationships from scratch B. Creating a customer list form with dynamic sorting and a reusable form for customers and employees C. Migrating data from Excel to Access D. Implementing encryption for Access databases Q7. What is not the focus of this class? A. SQL Server database management B. The SQL language as used in Access C. Dynamic SQL statements in forms D. Select queries in Access Q8. If you need to learn the basics of creating tables, queries, forms, and reports, you should: A. Take this seminar immediately B. Look for specialized SQL Server content C. Start with the Access 100 series or Beginner course D. Ignore prerequisites Q9. Which versions of Access is the seminar primarily demonstrated on, and which are compatible? A. Access 2007 only with limited compatibility B. Access 2010, but users of 2007 and 2003 can follow along C. Only Access 2003 and earlier D. Access 2002 and earlier versions only Q10. In which lesson do you learn about parameter queries and the Like keyword? A. Lesson 1 B. Lesson 3 C. Lesson 4 D. Lesson 5 Q11. What is covered in Part 2 of this seminar series? A. Only form design techniques B. Action queries, joins, and aggregate functions like Sum and Max C. Ribbon interface customization D. Data encryption and security Q12. Where can students ask questions and get help related to the seminar? A. Personal email only B. Access forums and the student forum available with the video player C. At local Access user groups only D. Through Microsoft tech support Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-A; 8-C; 9-B; 10-C; 11-B; 12-B 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. SummaryToday's video from Access Learning Zone introduces the Access SQL Seminar Part 1. My goal with this seminar is to help you master the SQL programming language so you can make your Microsoft Access databases more powerful. By the end of this session, you'll be able to add new features to your forms and reports, reduce the clutter caused by extra saved queries, and unlock advanced query features in Access that are only available if you know SQL, like creating Union Queries.In this course, I start by explaining what SQL is, what the acronym stands for, and why having a solid understanding of it is so valuable when you want to get more out of your Access databases. I will describe the different elements that make up an SQL statement, such as Select, From, Where, and Order By clauses, and I will discuss the various operators, the Between keyword, wildcard characters, and much more. After the initial theory and some simple examples, we will go through two practical projects in Access. In the first project, we will create a customer list form that displays customer information. With just two buttons, you will be able to change how the list is sorted, either by last name or by credit limit, using dynamic SQL statements. For the second practical example, I will demonstrate how you can use the same form for different sets of information. With some smart SQL, you could use a single form to display just customers, just employees, or both, depending on the SQL statement applied to the underlying data. While this seminar focuses on SQL as it applies to Microsoft Access, most of what you'll learn is relevant to almost any database system that uses SQL. There might be a few differences here and there, and I will point out some of the main ones, but in general, once you know SQL, you can apply that knowledge in many database environments. It's important to point out that this seminar is not about Microsoft SQL Server. SQL Server is a specific database product from Microsoft, and that will be covered separately. This session is focused just on the SQL language itself, though the language skills you learn here will be very helpful if you decide to work with SQL Server later on. For those who plan on working with databases that require higher security or handle large volumes of data, I do still suggest eventually looking into SQL Server as your database backend. Future courses of mine will cover that in detail. Before we get started, let's talk about prerequisites. This is not an introductory Access class. I assume you already know how to create tables, queries, forms, and reports in Access. If you are still new to those topics, have a look at my Access Beginner series, previously called Access 100 classes, for the very basics. The Expert series, which used to be called the 200 series, gets into more intermediate topics, and the Advanced series (or 300 series) covers VBA programming and more. You do not have to be an expert in VBA to follow along here, but some experience will help, especially for the last couple of lessons involving some light programming. As we move into lessons with programming examples, I will guide you step by step. So even if you are new to programming, it shouldn't be too difficult to keep up. The principal focus is on SQL, but I will also show how to apply it practically, which includes writing a little code at the end. Also, as a quick note, as of now, I am in the process of updating the course names for Access 2010. If you are looking for my 101 or 201 classes and cannot find them, search for courses named Beginner and Expert instead, since those are the new titles. Do not be confused by the name change if you see it on the website. Throughout this seminar, I will be using Access 2010. If you are using Access 2007, you will have no trouble following along, as the content is nearly identical. Those of you on Access 2003 or even earlier versions will find that most of the material still applies, especially on the SQL and VBA side. The biggest differences are where some buttons have moved in the newer versions, so I'll help you find those as needed. Let me give you a quick overview of what we will cover in each lesson. Lesson 1 introduces SQL and explains its importance. Lesson 2 goes over the components of a basic SQL statement, including Select, From, Where, and Order By. Lesson 3 starts exploring more complex Where clauses. Lesson 4 moves further into Where clauses and covers parameter queries, the Like keyword, wildcards, the In clause, and character ranges. Lesson 5 takes a look at the Order By and Distinct clauses. Lesson 6 brings together everything we have learned so far with a hands-on project. We will build a customer list form featuring a list box and create buttons that dynamically change how the data in the list is sorted, either by last name or credit limit, using dynamic SQL and some VBA. Lesson 7 offers another practical project that shows how dynamic SQL can be helpful. We will set up a name list form that can display customers, employees, or both at once in a continuous form, all using a union query. This session is Part 1 of a three-part SQL training series. It focuses on the basics and mainly on select queries, which means retrieving data from your tables. In Part 2, we'll get into action queries for manipulating data (similar to append and delete queries in Access), as well as working with joins and learning about various aggregate functions like Sum, Max, Min, and others. Part 3 will cover using SQL to actually modify your database structure, such as changing table or query designs, which requires more advanced knowledge. Detailed outlines for each part are available on my website. If you have any questions about today's material, feel free to post them in the Access Forum on the website. We have an active community of students and users who can help answer questions and share tips, and I check in there frequently myself. There's a general forum you'll find on the main website menu, and for this course, you'll notice a student forum option alongside the video content where you can post your questions and receive help more directly. If you have other questions that are not answered in the forums, you can always contact us directly through accesslearningzone.com contact. Get comfortable and prepare to enhance your SQL skills with Access. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListWhat SQL is and why it is importantSQL syntax and key terminology Understanding the SELECT clause Using the FROM clause in SQL Implementing the WHERE clause Applying the ORDER BY clause Using different operators in SQL Working with the BETWEEN keyword Using wildcard characters in queries Building complex WHERE clauses Parameter queries and the LIKE keyword Using the IN clause and character ranges Applying the DISTINCT clause Creating an unbound customer list form with SQL Sorting data dynamically with SQL and VBA Using dynamic SQL statements in forms Switching data sources with SQL in forms Displaying customers, employees, or both with UNION Building a name list form using dynamic SQL Differences and similarities in SQL across databases Practical Access SQL examples |
||||||||||||||||||||||||||||||
|
| |||
| Keywords: Access SQL 1 Seminar PermaLink How To Write Select Queries, Use WHERE and ORDER BY Clauses, and Build Dynamic Forms in Microsoft Access |
