Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Microsoft Access SQL Seminar, Part 1
 
Learn the basics of the SQL programming language
Build SELECT queries and enhance your Access databases

 

 

This 1 hour, 38 minute video 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.

Learn More...

Click on the video image to the right to learn more about exactly what's covered in this seminar. The video is about 9 minutes long and fully explains everything that's discussed. Then, continue reading the course outline below if you have further questions.

AccessLearningZone.com
  9 minutes
 
Seminars - Access SQL Part 1
Description: Learn the basics of the SQL programming language to enhance your Microsoft Access database projects
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 329 very helpful
Running Time: 1 hours, 38 minutes
Cost: $59 - Order multiple courses to receive a discount up to 50% off
Includes a sample, customizable, working database, as built in class.

 

This seminar has several goals. You will:

1. Learn about SQL and what it is
2. Discover why you should learn to program in SQL
3. Create basic SQL SELECT statements
4. Learn the FROM, WHERE, and ORDER BY clauses
5. Use the SQL operators and keywords such as AND, OR, and IS NULL
6. Work with the LIKE command and wildcard characters

7. Learn the IN, DISTINCT, DISTINCTROW, and other commands
8. Build a list box with a dynamic SQL sort
7. Create a form with a dynamic SQL RowSource

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.
 

 

Access SQL Seminar 1 Outline

00. Introduction (9:03)

01. What is SQL (6:09)
What is SQL
Structured Query Language
How to see SQL in queries
Why learn SQL

02. Basic SQL Components (14:27)
Build Customer Table
Build a simple query using QBE Grid
SQL View
SELECT statement
FROM clause
ORDER BY clause
WHERE condition
Changing fields in SELECT statement
Showing all fields with *
Clauses on separate lines
Semicolon
No spaces in field names

03. WHERE Clauses 1 (16:00)
Operators: = > < <= >= <>
String values and quotes
TRUE, FALSE, Yes, No, -1, 0
Dates in # signs
BETWEEN with dates, text, numbers
AND, OR with multiple fields
IS NULL
IS NOT NULL

04. WHERE Clauses 2 (13:23)
Parameter Queries
Parameters with BETWEEN
LIKE keyword
Wildcard characters: *, ?, #
Different wildcards: % and !
Range of Characters [A-P]
Not in a range [!A-P]
IN clause

05. ORDER BY and DISTINCT (6:49)
ORDER BY
Multiple fields
ASC, DESC
DISTINCT
DISTINCTROW

06. Customer List Form (15:22)
Build an Unbound Form
Add a Customer List Box
RowSource Property
Change SQL Dynamically
Command Buttons with VBA Code
Docmd.OpenForm

07. Name List Form (11:23)
Form that gets data from 2 tables
Show Customers
Show Employees
Show BOTH
UNION Query
Me.RecordSource
Me.Caption property

08. Review (5:13)

 


 

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP