|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
SQL 2 Seminar Lessons Welcome to Access SQL 2. Total running time is 4 hours, 58 minutes. Lessons
Resources
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 advanced SQL techniques for Microsoft Access, including action queries to update, add, and delete records, using append and make-table queries, and building dynamic forms to automate processes. We will cover joins such as inner, left, right, and self-joins, explore aggregate queries for sums and averages, work with aliases, learn about functions like string and date functions, and build forms with cascading combo boxes. You will also see how to use the TOP keyword, UNION queries, and calculations inside your SQL statements. This is part 2.TranscriptWelcome to the Access SQL Summoner Part 2, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.This seminar picks up where the SQL Part 1 seminar left off. You will learn more about Action Queries in SQL, which is using SQL to update and change your data. You will learn how to edit records, add records, create a new table, add records onto an existing table, and delete records. You will learn about aliasing, which is creating a shorthand for your field and table names. You will learn how to work with the different types of joins to bring multiple tables together, which will build a crosstab query, so you can see your data across multiple axes. You will learn about aggregate queries, using sums and averages and counts to get more information out of your data. You will also learn how to work with different functions inside of your SQL queries, such as string manipulation functions, date/time functions, and lots more. As I mentioned a minute ago, this is Part 2 of a 3-part series on SQL. It is strongly recommended that you take Part 1 of my seminar before taking this part. There is a lot of good fundamental information about SQL and exactly what this seminar series covers in Part 1, so do not miss that. For more information on this seminar, go to accesslearningzone.com and look for my access seminars list. Part 1 of the series covered the SQL basics, mostly select queries. This Part 2 will cover action queries, joins, functions, and a lot of the techniques I mentioned earlier. Part 3, the next part in the series, will cover mostly table and query design, actually building tables and queries from inside your SQL code. I will be using Access 2010 for this seminar. If you are using 2007, you should not have any problems following along. The material is also valid for previous versions of Access. The screens are a little different, but all the materials are the same, and I will point out any major differences where applicable. Now let's take a minute to go over exactly what is covered in each lesson of the seminar. In lesson 1, we will learn about update queries. We will build an update query using the classic Access query designer, and then we will see how the SQL for an update query works. In lesson 2, we will continue on with update queries. We will see how to run an update query that joins data from multiple tables. In lesson 3, we will begin building a form to allow us to automate the updating of our product pricing based on the pricing that our vendor sends us. In lesson 4, we will build an update pricing button where we can launch our custom SQL statement to run the update query. In lesson 5, we will talk about append queries, or an insert into query in SQL, and I will show you how to use the append query to create a log function so we can track different user activities in our database, such as when they log on or open a form. We will track that by adding single records with values using an append query. In lesson 6, we will continue working with append queries. Instead of just copying one record at a time, however, I will show you how to copy a bunch of records from a table into another table. In lesson 7, we will learn how to use a make-table query to create time-based backups of our tables. Since we know how to manipulate SQL statements directly, we can make the table name that is created have a time and date stamp. In lesson 8, we will create a delete query to delete inactive products from our product table. In lesson 9, we are going to learn about the TOP keyword in SQL, where I can say, show me the top 5 records, the top 10 records, or the top 50 records. We will learn the SQL for it, and we will start to build a form where the user can pick what they want to see, show me the top 3, what field you want to sort it by, and so on. In lesson 10, we are going to continue building the top X items form that we started in the last lesson. We will add some more parameters to it, we will learn some text functions to work with our SQL, and we will build a report that I will show you how to synchronize with the form to show the same records. In lesson 11, we will learn about aliases, where you can change the field names and the table names in your query on the fly. In lesson 12, we will begin learning about joins. We will learn about the different types of joins, and we will see how inner joins work in detail. In lesson 13, we will do more with joins. We will learn about the left and right outer joins, we will talk about full joins, and we will see an example of a self-join, where we will join employees to itself, where we can have employees and supervisors. We will learn more about Cartesian products, and I will show you an example where we will make a softball schedule, where every team will not play itself. In lesson 14, we will learn more about the IN clause that we began learning about in part 1 of the series, and in this lesson, we will learn how to use a select statement inside of an IN clause. In lesson 15, we are going to cover the UNION query, which allows us to union together the results from multiple tables into one query. In lesson 17, we are going to talk a little bit about calculations in SQL, including the basics like addition, subtraction, multiplication, and division, but we will also talk about integer division and modulus. In lesson 18, we are going to learn about string concatenation, which is adding strings together, and I am going to show you a neat SQL trick. In lesson 19, we will take a look at aggregate queries, where we can group and total based on various criteria. Continuing on with aggregate queries in lesson 20, we will learn about the WHERE and EXPRESSION options in SQL, and we will learn how to use an SQL statement as a WHERE condition. In lesson 21, we are going to start looking at functions that you can use in queries. We will start off with string functions. Lesson 22 will cover most of the popular time and date functions that you use in Access queries. Lesson 23 is going to cover all the rest of the functions that I use on a regular basis, including some math functions and some conversion functions. In lesson 24, we are going to learn how to build a form with cascading combo boxes. We will use SQL statements in our combo boxes. When the user picks a country, it will filter the state box to only show states or provinces from that country. Then when a state is picked, it will only show cities, in this case, offices, from that state. That is cascading combo boxes. In the last lesson, we started top down. We picked a country and went down to state and then city. What happens if you have to go the other way around? What happens if you know the city and you need to show a list of valid states and the country for that city? In this lesson, we will cascade our combo boxes the other way and we will learn a new function called the lookup. If you have any questions while you are taking this seminar, please feel free to post them in the Access forum on my website. You will find we have a very active community of users and either myself or one of them will be happy to answer your questions. If you have any other questions, feel free to contact us at accesslearningzone.com/contact. Now sit back and relax and get ready to continue learning SQL. QuizQ1. What is the primary focus of Access SQL Summoner Part 2?A. Advanced database normalization techniques B. Action queries, joins, functions, and other SQL techniques C. Designing forms and reports only D. Data security and encryption Q2. What topic is specifically covered in lesson 1 of the seminar? A. Make-table queries B. Select queries basics C. Update queries using the query designer and SQL D. Dealing with relationships between databases Q3. Which SQL topic will you learn about in lesson 8? A. Form design B. Calculated fields C. Delete queries for removing records D. Lookup fields Q4. What is aliasing in the context of Access SQL as described in the seminar? A. Encrypting field names B. Creating a shorthand for field and table names C. Copying tables to new databases D. Aggregating query results Q5. Which concept is introduced when discussing crosstab queries in the seminar? A. Inner joins B. Multi-table joins to view data across multiple axes C. Append queries D. Calculated controls on forms Q6. What does the seminar recommend regarding the order in which to take the course parts? A. Start with any part you want B. Take Part 3 first C. Take Part 1 before Part 2 D. Only take Part 2, since others are optional Q7. What is the purpose of aggregate queries as explained in the seminar? A. To split tables into multiple smaller tables B. To perform calculations like sums, averages, and counts on records C. To rename database objects D. To validate user input Q8. What is demonstrated in lesson 7? A. Using forms and reports B. Creating time-based backups with make-table queries C. Formatting query output D. Importing data from Excel Q9. Which lesson in the seminar teaches about the SQL TOP keyword? A. Lesson 5 B. Lesson 9 C. Lesson 14 D. Lesson 21 Q10. What feature is described as filtering combo box options based on previous selections in forms? A. Totals rows B. Lookup relationships C. Cascading combo boxes D. Referential integrity Q11. What kind of SQL functionality does the seminar teach regarding string handling? A. Only basic number functions B. Advanced pivot queries C. String manipulation and concatenation functions D. Constraints and keys Q12. When you want to combine results from multiple tables into a single query, which SQL feature will you use, as covered in the seminar? A. GROUP BY B. UNION query C. Subquery D. Data import wizard Q13. What example is used when demonstrating self-joins in the seminar? A. Products and vendors B. Employees and supervisors C. Orders and customers D. Cities and countries Q14. Which function is showcased to allow queries to filter based on dynamic options like date and time? A. VBA scripting B. Make-table query C. Time and date functions in SQL D. Database splitting Q15. What is the suggested resource for getting help and interacting with other seminar students? A. Official Microsoft support only B. The Access forum at accesslearningzone.com C. Stack Overflow forums exclusively D. Emailing Microsoft Access development team Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B; 9-B; 10-C; 11-C; 12-B; 13-B; 14-C; 15-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 continues the Access SQL Summoner series with Part 2. I am your instructor, Richard Rost, and in this seminar, I pick up right where the first part ended. We will explore more advanced aspects of SQL in Microsoft Access, focusing particularly on Action Queries. These are the SQL commands that actually alter your data, allowing you to update, add, or remove records, as well as create new tables or add information to existing ones.One important concept you will learn is aliasing, which lets you create shortcuts or alternative names for fields and tables within your SQL statements. This helps make queries shorter and sometimes easier to read. Bringing together information from multiple tables is another key focus in this course. Different types of joins are covered, allowing you to collect and analyze data across several tables. Using these joins, you will even build a crosstab query, letting you display your information on more than one axis. Aggregate queries are also introduced, so you can apply sums, averages, counts, and other calculations to gain more insight from your data. On top of that, we will use a variety of built-in functions within your SQL code, including functions for working with text, dates and times, and much more. As a reminder, this is Part 2 of a three-part SQL seminar series. If you have not taken Part 1 yet, I strongly recommend starting there. Part 1 provides all the fundamental knowledge necessary to understand SQL and what the entire seminar sequence covers. It will help you get much more out of this material. You can find information about both parts of the seminar on my website under the Access seminars section. To break down the overall series, Part 1 focused on the basics and select queries. Here in Part 2, the focus shifts to action queries, joins, and using various functions within SQL. Part 3, which comes next, will deal with designing tables and queries directly through SQL code. For this seminar, I use Access 2010. If you are working with Access 2007 or even older versions, you should still be able to follow along without any trouble. The appearance of the user interface might be slightly different, but the underlying material I teach remains consistent, and I will make note of any important differences as we go. Here is a brief summary of what you will find in each lesson: - Lesson 1 covers update queries. We start in the standard Access query designer and then investigate how these queries operate in SQL. - Lesson 2 continues with update queries and shows how to run them when joining information from multiple tables. - Lesson 3 moves into creating a form that lets you automate updating product prices based on data from your vendors. - Lesson 4 adds a dedicated button for updating prices, using a custom SQL statement within an update query. - Lesson 5 introduces append queries (which use SQL's insert into command). I demonstrate how to log user activities—such as when users log in or open forms—by appending individual records to a table. - Lesson 6 expands on append queries by showing how to copy many records from one table to another in a single step. - Lesson 7 explores make-table queries. Here, you learn to produce time-stamped backups of tables by directly manipulating SQL statements to name those backup tables with the current date and time. - Lesson 8 is about delete queries. You will learn how to remove inactive products from a product table. - Lesson 9 introduces the TOP keyword in SQL, which allows you to return only a certain number of top records. We start building a form that lets users pick how many records to show, as well as the field for sorting. - Lesson 10 continues developing the form from the previous lesson. We add more parameters, use some text-related functions within SQL, and build a report that can display the same records as the form. - Lesson 11 is dedicated to aliases—enabling you to change field and table names within your queries. - Lesson 12 marks the start of our lessons on joins. You will learn about the different types of joins, focusing first on inner joins. - Lesson 13 explores more advanced joins. We cover left and right outer joins, the idea of a full join, and include an example of a self-join. For instance, you will see how to match employees to supervisors in the same table. We will also talk about Cartesian products and see how to generate a softball schedule where no team plays itself. - Lesson 14 goes deeper into using the IN clause. Building on what was introduced in Part 1, this lesson will show you how to use select statements within an IN clause. - Lesson 15 introduces the UNION query, which lets you combine results from different tables into a single query result. - Lesson 17 covers calculations in SQL, from basic arithmetic to integer division and modulus. - Lesson 18 explains string concatenation, which is joining strings together. I will also show you a useful technique within SQL for this purpose. - Lesson 19 introduces aggregate queries, enabling you to group and total data along different criteria. - Lesson 20 continues with aggregate queries, focusing on the WHERE and EXPRESSION options in SQL, and demonstrates how to use SQL statements as WHERE conditions. - Lesson 21 starts a section on the different functions available within queries, beginning with string functions. - Lesson 22 looks at popular date and time functions that you frequently use in Access queries. - Lesson 23 introduces more advanced and commonly-used functions, like mathematical operations and data conversions. - Lesson 24 explains how to create a form with cascading combo boxes. These combo boxes utilize SQL statements so that when a user selects a country, the next box only lists states within that country, and when a state is chosen, only cities or offices within that state are shown. - Finally, our last lesson reverses the cascading process. Instead of starting with a country and narrowing down, you may want to start with a city and work upward to the associated state and country. In this lesson, I will show you how to make your combo boxes cascade in the opposite direction and introduce a handy lookup function. Throughout this seminar, if you run into questions or need help, I encourage you to participate in the Access forum on my website. There, you will find an active group of users ready to assist, and I am always happy to respond as well. If you prefer direct contact, you can always reach us through the contact page at accesslearningzone.com. So, settle in and get ready to expand your knowledge of SQL with Access. For a complete video tutorial, including step-by-step guidance on everything I've mentioned here, visit my website at the link below. Live long and prosper, my friends. Topic ListUpdate queries in SQLRunning update queries with joins Automating product price updates with forms Creating an update pricing button to launch SQL Append (Insert Into) queries for activity logs Appending multiple records between tables Make-table queries for time-stamped backups Delete queries to remove inactive products Using the TOP keyword in SQL queries Building a form to select top X records Synchronizing reports with a top X items form Using aliases for field and table names Explaining and using inner joins Left and right outer joins explained Full joins and self-joins with example Cartesian products in joins Using the IN clause with a select subquery Creating UNION queries to combine data sources Performing arithmetic calculations in SQL Integer division and modulus in SQL String concatenation techniques in SQL Aggregate queries for grouping and totals Using WHERE and EXPRESSION in aggregate SQL String functions in SQL queries Time and date functions in Access SQL Math and conversion functions in SQL Building forms with cascading combo boxes Creating reverse-cascading combo boxes and lookup functions |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: Access SQL 2 Seminar PermaLink How To Use Update, Append, Delete, Joins, Functions, Aggregate, and More Queries in Microsoft Access |
