Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Seminars > SQL Language > SQL Part 2 > < SQL Part 1 | SQL Part 3 >
 
Access SQL Seminar Part 2

Supercharge your Databases Using Action Queries


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Buy Now

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. Thanks.

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

 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

5/16/2022Referential Integrity
5/15/2022Invoicing Member Discussion
5/13/2022Multivalued
5/12/2022Number Field Size
5/11/2022Create Word Document
5/11/2022Export to Word
5/10/2022On Click Event Timing
5/9/2022Invalid Use of Null
5/7/2022Event Handler
5/6/2022Database Title & Icon
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access seminar 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, piv  Page Tag: whatsnew  PermaLink  Access SQL Seminar Part 2