|
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)
|
