|
This
seminar has several goals. You will:
1.
Learn about Action queries and when to use them
2.
Discover how to launch queries from VBA
code
3.
Create Aliases for your field and table names
4.
Learn the different types of Table Joins
5.
Construct a Union query
6. Work with a Crosstab query
7. Perform calculations in your SQL
statements
8. Explore the different Aggregate query
functions
9. Utilize string, math, time/date, and other
query functions
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.
Access SQL Seminar 2 Outline
00. Intro (8:32)
01. Update Query 1 (14:47)
Four types of action queries
Update query
Create Product Table
Update query in Access QBE designer
Update with no parameter
SQL View
UPDATE SET
WHERE
Updating multiple fields
02. Update Query 2 (10:27)
Update Query with Multiple Tables
INNER JOIN ON clause
Update vendor product pricing
03. Update Query Form 1 (19:21)
Markup Text Box
Category Combo
SQL Rowsource for Listbox
Custom WhereStr
04. Update Query Form 2 (14:20)
Default markups for each category
Column() combo box property
Docmd.RunSQL
Run update query to perform markup
Format function
05. Append Query 1 (19:21)
Create a Log Table
Append Query with no input table
INSERT INTO SELECT
INSERT INTO VALUES
NULL
VBA Optional for sub parameters
DateTime reserved keyword
Numeric values
06. Append Query 2 (12:48)
Create ProductArchiveT
Archive historic product info
INSERT INTO SELECT FROM
WHERE Condition
07. Make Table Query (6:19)
SELECT INTO FROM
Creating time-based table backups
08. Delete Query (7:04)
Add IsActive to Listbox
DELETE FROM WHERE
09. Top X Items 1 (13:35)
SELECT TOP
SELECT TOP PERCENT
Top X Records Form
10. Top X Items 2 (15:42)
Custom TopStr
InStr function
Replace function
Ascending Descending Combo
Open Report with Custom SQL
Forms!FormName!Field.Property
.RowSource
.RecordSource
11. Aliases (9:57)
SELECT AS
Cartesian Product
Alias field names
Alias table names
AS optional with table names
12. Joins 1 (10:24)
Types of Joins
Inner Join
Equi-Join
Left, Right Outer Join
Full Join
Cartesian Join
Self Join
INNER join two tables
Joining multiple tables
13. Joins 2 (11:33)
LEFT JOIN
Left Outer Join
Right Outer Join
Full Joins not support in Access
SELF Join
Employees and Supervisors
Alias in the Access QBE grid
Cartesian Example
Softball Team Schedule
14. More with IN (4:08)
IN with SELECT statement
NOT IN
15 Union Query (11:10)
UNION
UNION ALL
Simulating a Full Join Query
16. Crosstab Query (9:01)
TRANSFORM
PIVOT
CrossTab like a PivotTable
Orders by State by Month
17. Calculations (5:13)
Operations
Addition, Subtraction
Multiplication, Division
Exponentiation
Integer Division
Modulus
18. Concatenation (5:45)
Ampersand
Plus with NULL values
19. Aggregate Queries 1 (13:17)
Sum
Avg
Count(Field)
Count(*)
Max
Min
First
Last
Total Row
GROUP BY
Month function
Criteria on Group By Field
Criteria on Aggregate Field
HAVING keyword
Nested Query
20. Aggregate Queries 2 (11:14)
Where option
Expression option
SQL statement as WHERE condition
Show all below average orders
21. Functions 1 (9:17)
String Functions
LTrim
RTrim
Trim
Left
Right
Mid
Len
InStr
UCase
LCase
StrConv for proper case
22. Functions 2 (15:16)
Date & Time Functions
Format function
Format parameter values
Date
Time
Now
Month
Day
Year
Hour
Minute
Second
DatePart
TimePart
DateSerial
TimeSerial
DateAdd
DateDiff
23. Functions 3 (11:36)
Misc Functions
ABS
INT
FIX
ROUND
SQR
IsNull
Nz
IIF
CSTR
CINT
CLNG
CDBL
CCUR
CDATE
CDEC
24. Cascading Combo 1 (12:51)
Country > State > City
SQL RowSource
AfterUpdate
25. Cascading Combo 2 (10:39)
Reverse the order
City looks up state
State looks up country
Boxes requeried
DLOOKUP
26. Review (4:18)
|
