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)