Access SQL Seminar Part 2
Supercharge your Databases Using Action Queries
Welcome
This seminar will pick up where Part 1 left off. You will learn more about the SQL programming language. We will focus on action queries, aliases, joins, crosstabs, union queries, aggregate functions, and more.
 Resources
Main Seminar Goals
- Learn about Action queries and when to use them
- Discover how to launch queries from VBA code
- Create Aliases for your field and table names
- Learn the different types of Table Joins
- Construct a Union query
- Work with a Crosstab query
- Perform calculations in your SQL statements
- Explore the different Aggregate query functions
- Utilize string, math, time/date, and other query functions
Topics Covered
We will begin by learning about Action queries. We'll start with an update query to change the data in a table. We'll also learn how to work with an update query that joins data from multiple tables.
Next we will build a form that we can use to automatically update our product prices based on new vendor pricing. We'll use an update query and inline SQL code to do this dynamically. The vendor will give us a table with new product pricing (with his product codes) and we'll have to join those products to our product table. We'll also set a default markup for each product category and be able to update the prices based on this markup. This is a great example.
Next we'll use an Append query to create a system log that will track everything that happens in the database. You'll also learn how to use an append query to make a batch-backup of large groups of product information - for example, saving historical pricing on all of your products so you can go back and see what you were selling a specific product at 2 years ago.
You will learn how to use a Make-Table query to make time/date-stamped backups of your tables. You should do this any time you plan on making changes to a table, or running a potentially dangerous action query. Safety first. This is also good for saving multiple copies of your customer table so you can refer back and compare changes that were made later.
You will learn how to use a Delete query to remove inactive products from your database (after archiving them or backing them up first, of course).
Next we'll learn how to use the TOP X command so you can see the top 10, top 5, top 50%, (and so on) of records in your table. We'll make a form where the user can choose how many records to view, which field to sort by, and how to sort (ascending or descending). We'll use inline SQL to display the results in a form PLUS we'll make a button to generate a printed report with the same criteria.
Next, we'll learn how to create Aliases to make referring to our field and table names easier. This also allows you to change the name of a field on the fly.
You will learn about all of the different kinds of query Joins. There are inner joins, outer joins, left joins, right joins, self joins, full joins, cartesian products, and lots more you can do when you join two or more tables together.
I'll show you a nifty example of a join where you can have one table with team names in it and with just one simple SQL statement generate a schedule of games where each team plays each other team once - but not itself.
You'll learn more about the IN function; specifically how to nest an SQL statement inside of the IN function to provide its list of values. You'll also see how NOT IN works.
We'll create a Union query where you can take multiple different tables and join them all together in one result set. This is great if you want to create a mailing list that includes customers, employees, vendors, and everyone else who has a name and address. Union queries are impossible to build in Access unless you know SQL.
You will learn how to perform mathematical calculations in your SQL statements. Remember, if it's something you can calculate on the fly, you usually don't need to save it in your table. Keep your databases small and efficient.
We'll cover string concatenation, and a really cool trick that you can use in SQL to ignore null strings that doesn't require any fancy functions. For example, notice the third record below is missing a middle initial. Not a problem.
You will learn how to use Aggregate queries to group and total based on various functions (sum, average, max, min, etc.) and criteria. You'll also learn some techniques that are specific to SQL and are very difficult to produce with the standard Access query designer. Notice below I'm using an SQL statement as the WHERE condition inside another SQL statement. This will show all of the orders that are over the average of all of the order totals. Great SQL trick.
Next, we'll cover many of the popular functions that I like to use with my SQL queries. We'll start with the string functions such as Trim, Left, Right, InStr, Len, UCase, LCase, and StrConv - which can convert to "proper" name case.
You will learn all of my favorite date and time functions, like DatePart, DateSerial, DateAdd, DateDiff, and lots more. I'll show you how to take a date/time value apart to get its components, and I'll show you how to put together another date using those component parts. We'll learn how to add and subtract dates to determine things like "what date is exactly 6 months in the future from the sale date?"
Next, we'll cover a bunch of additional queries like ABS, Int, Fix, Round, Sqr, IsNull, Nz, IIF, and the conversion functions like CStr and CCur. This, and the previous 2 lessons, will teach you about 95% of the functions you will need to use in your queries on a regular basis.
Finally, we'll tie everything together with one of my favorite example lessons. I will show you how to make a three-level set of cascading combo boxes. This is where you pick a country and then the state combo box is filtered to show you just states from that country. Then the same thing happens to the next box which shows you just the offices that are in cities in that state. This all happens with custom SQL written in the AfterUpdate events of these combo boxes.
But that's not all. Not only will we go top-down, but I'll also show you how to go bottom-up. If you move to a different record, I'll show you how to take the office/city that you know and use it to look up the state and country and then dynamically refresh and update all three combo boxes. This is real cool stuff!
Again, this seminar is perfect for anyone who wants to learn how to supercharge their Microsoft Access databases with the added power of custom SQL. It is the second part of a three-part series. 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
Access SQL Seminar Part 1 is strongly recommended, but not required.
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, docmd.runsql, insert into, null, select into, delete, delete from, select top, select top percent, rowsource, select as, cartesian, alias, full join, self join, left join, right join, in, not in, union, union query, crosstab, transform, pivot, concatenation, aggregate, sum, avg, count, max, min, group by, having, cascading combo boxes
Intro In this video, we continue exploring Microsoft Access SQL with a focus on action queries, including how to update, append, and delete records using SQL. You will learn about aliasing for field and table names, different types of joins, aggregate queries using functions like SUM and COUNT, and techniques for building forms with dynamic SQL features such as cascading combo boxes. We will also work with string and date functions, explore the TOP keyword, and see practical SQL applications like creating backups and tracking user activity. This is part 2.Transcript Welcome 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, 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 more.
As I mentioned earlier, 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. 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 us 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 with update queries and 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 them one record at a time, I will show you how to copy a bunch of records from one 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, 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, learn some text functions to work with our SQL, and 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, learn about the different types of joins, and 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, talk about full joins, and see an example of a self-join, where we will join Employees to itself so 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. 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, relax, and get ready to continue learning SQL.Quiz Q1. What is the main focus of Access SQL Summoner Part 2?
A. Building advanced forms in Access
B. Learning more about Action Queries in SQL
C. Programming VBA modules
D. Designing reports only
Q2. Which of the following topics is NOT specifically mentioned as covered in Part 2 of the seminar?
A. Action Queries
B. Advanced VBA debugging
C. Aliasing field and table names
D. Using SQL functions
Q3. What should you do before starting Part 2 of the seminar?
A. Take a course in database administration
B. Complete Part 1 of the seminar series
C. Learn XML basics
D. Upgrade to the latest version of Access
Q4. What are Action Queries used for in SQL?
A. Searching for records
B. Changing or updating data
C. Formatting output
D. Exporting data to Excel
Q5. Which SQL topic will you learn about in Lesson 1?
A. Make Table queries
B. Update queries
C. Joins
D. Union queries
Q6. In which lesson do students learn how to automate product pricing updates?
A. Lesson 3
B. Lesson 5
C. Lesson 8
D. Lesson 11
Q7. What is the primary purpose of an append query in Access SQL?
A. Renaming tables
B. Creating a log of user activities by adding records
C. Deleting old data
D. Sorting records
Q8. Which lesson covers creating backups with a date and time stamp using Make Table queries?
A. Lesson 11
B. Lesson 5
C. Lesson 7
D. Lesson 14
Q9. What does the TOP keyword in SQL allow you to do?
A. Update only the last record
B. Show a limited number of top records
C. Sort data alphabetically by default
D. Group data by category
Q10. Which lesson covers the use of aliases for field and table names?
A. Lesson 11
B. Lesson 6
C. Lesson 20
D. Lesson 19
Q11. What type of join will you learn about in Lesson 13 when joining Employees to itself?
A. Outer join
B. Self-join
C. Full join
D. Three-way join
Q12. What is the purpose of the IN clause in SQL, as described for Lesson 14?
A. To insert new records
B. To filter records using a list or subquery
C. To make tables
D. To calculate sums
Q13. What is the main function of a UNION query in SQL?
A. To update all matching records
B. To merge results from multiple tables into one
C. To create a backup
D. To format field names
Q14. In Lesson 18, what concept is introduced for combining strings in SQL?
A. String trimming
B. String concatenation
C. String searching
D. String padding
Q15. What are aggregate queries used for?
A. Formatting field names
B. Grouping and totaling data based on criteria
C. Deleting duplicate data
D. Creating relationships
Q16. What do cascading combo boxes allow you to do in a form?
A. Hide unused fields
B. Dynamically filter choices in one combo box based on another
C. Automatically delete records
D. Change database password
Q17. What should you do if you have questions during the seminar?
A. Contact your internet provider
B. Post them in the Access Forum on the website
C. Email Microsoft support
D. Wait until the final lesson
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-A; 7-B; 8-C; 9-B; 10-A; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B; 17-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.Summary Today's video from Access Learning Zone continues the SQL Summoner seminar series with Part 2, where we take your knowledge of SQL in Microsoft Access to the next level. In this course, I will show you how to expand on what you learned in Part 1, with a focus on action queries and more advanced SQL concepts inside Access.
We will start by covering action queries, which let you modify data through SQL. By the end of this seminar, you will understand how to edit existing records, add new records, create new tables, and append data to existing tables. You will also see how to remove records using delete queries.
We will talk about the concept of aliasing, which allows you to create easy-to-use names for your fields and tables within queries. Joins will also be a major topic; bringing multiple tables together is essential for more complex data analysis. These join techniques will form the foundation for building crosstab queries, making it possible to visualize your data across multiple dimensions.
Aggregate queries are another highlight in this seminar. These allow you to perform calculations on groups of data, such as generating sums, averages, and counts. You will also learn to use a variety of built-in Access functions within your SQL queries, including those for manipulating text, working with dates and times, and more.
It is important to note that this seminar is the second part of a three-part series. I strongly recommend you complete Part 1 before beginning this one, since it covers all the foundational details of SQL, including what you can do with the language from inside Access. For more details about this seminar or to explore the full list of my Access courses, you can visit AccessLearningZone.com.
Let me give you a structured overview of what we will cover in each lesson of this part of the seminar.
Lesson 1 focuses on update queries. Together, we will use the Access query designer to build an update query and then break down the SQL code that drives it.
Lesson 2 continues with update queries, but here you will see how to run an update that pulls in data from multiple tables.
In Lesson 3, we will develop a form to automate enterprise pricing updates based on vendor-supplied pricing.
Lesson 4 introduces a custom button that will launch a SQL statement specifically to handle those price updates automatically.
In Lesson 5, I will introduce append queries (also known as INSERT INTO queries in SQL). We will use these queries to create a logging function, recording specific user activities in your database, such as logging in or accessing forms.
Lesson 6 expands on this concept by showing you how to copy multiple records from one table to another using append queries instead of copying one record at a time.
Lesson 7 introduces Make Table queries, which are perfect for generating backups of tables. Using SQL, you can even customize the names of these backup tables to include a timestamp.
Lesson 8 gives you a practical example of a delete query, which you can use to remove inactive products from an inventory table.
With Lesson 9, you will explore the TOP keyword. This lets you view just the top few records in a dataset, for example, the top 5, top 10, or another number you choose. I will also introduce a simple interface for users to select how many top records they want and what field to sort by.
In Lesson 10, we will further develop this feature, adding more parameters and using text functions within our SQL. I will also show how to sync this feature with a report, so the displayed records and printed reports match up.
Lesson 11 is about SQL aliases for fields and tables, which can be very useful to improve clarity or simplify your query syntax.
Lesson 12 marks the beginning of an in-depth look at joins. We will cover the essential types and thoroughly explore how inner joins work.
Lesson 13 dives deeper into more advanced join types, including left and right outer joins, the concept of a full join, and even self-joins, demonstrated with an example where employees are linked to their supervisors. We will also look at Cartesian products, and as a fun exercise, create a softball schedule ensuring every team plays each other without duplicating matches.
Lesson 14 revisits the IN clause first discussed in Part 1. Here, you will learn to nest a SELECT statement inside an IN clause for even more flexible querying.
In Lesson 15, we will examine UNION queries, which allow you to combine results from multiple tables into a single result set.
Lesson 17 will cover calculations in SQL, including the usual arithmetic operators and some extra techniques like integer division and the modulus operation.
Lesson 18 puts the spotlight on string concatenation in SQL and includes a helpful trick that you can use with textual data.
In Lesson 19 and the following lessons, we will explore aggregate queries that group and total data based on different criteria.
Lesson 20 specifically covers the WHERE and EXPRESSION options available in SQL, and you will see examples of using one SQL statement as a WHERE condition inside another.
In Lesson 21, I will introduce a variety of string manipulation functions that are available for use within your SQL queries.
Lesson 22 is dedicated to time and date functions commonly used in Access queries.
Lesson 23 wraps up the function segment by covering math and conversion functions that are particularly useful in data processing.
Lesson 24 will walk you through building a form with cascading combo boxes. This technique restricts the options in your combo boxes based on prior selections, such as selecting a country to filter states, then selecting a state to filter down to cities or offices.
Lastly, I will guide you through a situation where the cascade works in reverse. For instance, if you know the city, you will learn how to display valid options for its state and country. Along the way, I will introduce the Lookup function, which is great for retrieving related values.
As always, if you have any questions during or after the seminar, you are welcome to post them on the Access Forum on my website. It is an active community and I, along with other members, will be happy to help.
You can also contact us through AccessLearningZone.com with any questions.
I invite you to get comfortable and prepare to advance your skills further as we continue learning SQL.
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 List Update queries using the Access query designer
SQL syntax for update queries
Update queries with multiple table joins
Automating product pricing updates with forms
Creating a button to launch update queries
Append queries and INSERT INTO in SQL
Using append queries to track user activities
Copying multiple records with append queries
Make Table queries for time-based backups
Creating tables with date and time stamps
Delete queries for removing inactive products
Using the TOP keyword to limit query results
Building a Top X Items form
Adding parameters and text functions to forms
Synchronizing reports with forms
Field and table aliasing in SQL
Inner joins and their usage
Left and right outer joins explained
Full joins overview
Self-joins and employee-supervisor relationships
Cartesian products and scheduling examples
Using the IN clause with subqueries
Creating UNION queries to combine results
Arithmetic calculations in SQL
Integer division and modulus operations
String concatenation in SQL
Aggregate queries for grouping and totals
WHERE and EXPRESSION options in aggregate queries
Using SQL statements as WHERE conditions
String functions in SQL queries
Time and date functions in Access queries
Math and conversion functions in SQL
Building forms with cascading combo boxes
Creating top-down cascading combo boxes
Reverse cascading combo boxes
Using Lookup function for cascading controls |
