| Expert Lessons |
After you've mastered the Beginner series, move up to the Expert Lessons to learn about table relationships and lots more.
|
|
| |
|
|
|
Relational Database Concepts
Primary & Foreign Key Fields
Types of Relationships
Focus on One-to-Many Relationships
Ad Hoc Query Joins
Left Joins to Show All Records
Relational Combo Boxes
Fixing Lead Source & Shipping Combos
Select a Customer on the Contact Form |
| |
|
|
| |
|
|
|
Database Table Normalization
Global Relationships
Working with the Relationships Window
Referential Integrity
Preventing Orphaned Records
Cascade Update & Delete
Table Subdatasheets
Setting Values Across Different Forms
Forms ! FormName ! FieldName |
| |
|
|
| |
|
|
|
SQL Basics
Future Callbacks for Customers
Default Value to Tomorrow at 9 AM
Creating Subforms
Link Master & Child Fields
Using SQL in Form Record Source
Form Footer Totals
Sum, Avg, Max, Min, Count
Modifying Embedded Macros |
| |
|
|
| |
|
|
|
Additional Form Controls
Option Groups, Tab Control
Toggle Buttons, Enhanced Formatting
List Box, Image Control
Display Image Without Storing
One-to-One Relationships
Extended Customer Details
More One-to-Many Relationships
Track multiple family members |
| |
|
|
| |
|
|
|
Print a Letter Report
Edit Embedded Macros
Dealing with Dirty Form Records
RefreshRecord Command
Export to Word, Excel, PDF, more
Email Report via Outlook
HyperlinkPart Function
EmailDatabaseObject Command
Customize the Ribbon |
| |
|
|
| |
|
|
|
Send Mass Mail from Access
Collection Letters
Automatic Date Ranges
New Report Properties
Force New Page
Multiple Letter Templates
Letters to All Active Customers
Allow Edits vs. Locked Fields
Send To Customers by Category |
| |
|
|
| |
|
|
|
Many-to-Many Relationships
Table Lookup Wizard
Multi-Valued Fields
Assign Customer to Multiple Groups
Junction Tables
Prevent Duplicate Records in a Query
Format a Report for Post Cards
Products from Multiple Vendors
Track Unit Cost and Profit |
| |
|
|
| |
|
|
|
Order Entry System
Order Form and Details Subform
Calculated Query Fields
Figuring Sales Tax if Taxable
IIF Function (If/Then/Else)
Proper Rounding of Values
Bankers Rounding
Nesting Functions
Final Product and Tax Totals |
| |
|
|
| |
|
|
|
Sharing Data Between Forms
Customer & Order Discount Rates
Default System Values Table
Multiple Sales Tax Calculations
SQL ALIAS
Printable Invoice Report
Inserting Subreports
Problems with Can Shrink
Open Report to a Specific Record |
| |
|
|
| |
|
|
|
Quotation or Invoice
Paid or Unpaid
Nested IIF Functions
The DLOOKUP Function
Sales Reps & Service Techs
DLOOKUP Criteria (ID, String, Date)
Display Values Direct from Tables
Calculated Table Fields
Expressions in Queries vs Tables |
| |
|
|
| |
|
|
|
Aggregate Queries
Sum, Avg, Count, Max, Min
Complex Query Criteria
Sales Totals by Month
Find Lowest Product Cost
Last Customer Contact Report
Employee Work Log, Timesheet
Calculate Hours Worked
DSUM Function |
| |
|
|
| |
|
|
|
Sorting & Grouping Levels
Build an Order List Form
Refresh vs. Requery
Employee Work Log Entry Form
Group Headers & Footers
Customers Grouped by State
Employee Hours by Week of Year
Sales Grouped in $500 Increments
Fix Collection Letter Reports |
| |
|
|
| |
|
|
|
Update Queries
Types of Action Queries
Report Where Conditions
Datasheet View vs Run Query
UCase, LCase, Proper Case
Deleting Tables with Relationships
Assign Sales Rep by State
Add 5% to all Product Prices
Update Paid Orders with Ship Date |
| |
|
|
| |
|
|
|
More Update Queries
Scrubbing (Cleaning) Data
Data with Inconsistent Formatting
Use Replace Function to Change Text
CStr, Nz, InStr, Left, Right Functions
Separate First & Last Names
Fix Non-Relational Tables
Append Queries
Daily Student Attendence Table |
| |
|
|
| |
|
|
|
Track Student Attendance
Put Customers / Students in Classes
Create a Daily Attendance Form
Use a Macro to Run Append Query
Disable System Warnings
Enable All Macro Actions
Attendance Reports by Student / Date
Delete Queries
Archive Old Customer Contacts |
| |
|
|
| |
|
|
|
Macro to Run Multiple Queries
Delete Query on Multiple Tables
Referential Integrity with Delete Queries
Make Table Queries
Backup Table to Another Database
Multiple Parent Student Mailings
Include Multiple Parents in Mailings
UNION Queries
Merge New Contacts with Archive |
| |
|
|
| |
|
|
|
Archive Old Unpaid Orders
Select & Mark Orders w Update Query
Create a Form to Review Orders
Append to Archive Table w Macro
Backup Customers, Orders, Details
Add "Write-Off" Notice to Customer Rec
Union Query for Reporting on Both
More with Embedded Button Macros
CrossTab Queries (PivotTables) |
| |
|
|
| |
|
|
|
Crosstab Query
Parameters & Criteria in Crosstabs
Fixed Column Headings
Find Duplicates Query
Partition Function, Create Tiers
Find Unmatched Query
Advanced Query Properties
Unique Values, Records
Cartesian Products |
| |
|
|
| |
|
|
|
Conditional Formatting
Advanced Conditional Expressions
Exporting to Microsoft Excel
Exporting to Microsoft Word
Microsoft Word Mail Merge
Exporting Text Files
Fixed Width vs. Delimited Text
Exporting to Other Databases
Exporting HTML Files for the Web |
| |
|
|
| |
|
|
|
Sending Mass Email from Access
Importing Data from Microsoft Excel
Linking Live to Excel Spreadsheet Data
Importing Text Files
Importing Data from HTML Pages
Importing and Exporting XML Data
Saving XML Schema Data (Table Info)
Linking to Outlook Folders
Reading/Editing Inbox, Contacts |
| |
|
|
| |
|
|
|
Import Access Database Objects
Linking to Other Access Databases
Splitting Your Database
Custom Front-End and Back-End DBs
Database Splitter Wizard
Shared Folder Permissions
Creating an Encrypted Front-End File
Preventing Design Changes
Record Locking in a Shared Environment |
| |
|
|
| |
|
|
|
Fixing Non-Relational Data
Split a Spreadsheet into Multiple Tables
Update Vendor Price Changes
Create Macro for One-Click Operation
Fix Our Order-Entry System
Product Change Don't Affect Orders
Build New Button Macro From Scratch
IF, GotoControl, GotoRecord
Manually Add Products Not In Database |
| |
|
|
| |
|
|
|
Remote Data Entry Database
View Info, Add Contacts Remotely
Synchronize With Main Server
Import Bank Transactions
Prevent or View Duplicates
Multi Field Composite Keys
Prevent Duplicate Sales Reports
Transfer Relational XML Data
Exporting Customers With Orders
Maintain Relationships via XML Files |
| |
|
|
| |
|
|
|
Import RSS Feed Data
Download Latest News from Web Sites
Display Weather Conditions
Use the WebBrowser Control
Display Images from a Web Page
Program Timer Events
Launch a Macro at Timed Intervals
Setup Online SQL Server Database
Create ODBC Connections & DSN Files
Share Access Data via the Internet |
| |
|
|
| |
|
|
|
String Functions
Left, Right, Mid, InStr, InStrRev, Len
LCase, UCase, Trim, RTrim, LTrim
Replace, Str, CStr, StrComp, ASC, CHR
Space, String
Logical Functions
IsDate, IsNull, IsNumeric, IsError
NZ, VarType, TypeName
And, Or, Not, Xor, IIF, Nested IIF
Switch, Choose |
| |
|
|
| |
|
|
|
Math Functions
Abs, Sgn, Round, Int, Fix, Val
Sqr, Exp, Log, Natural Logarithms
Calculate Pi, Degrees to Radians
Trig Functions: Sin, Cos, Tan, Atn
Mod, Integer Division, Is Divisible
Random Numbers: Dice Roller
Display Random X Records
Type Conversion Functions
CInt, CLng, CStr, CDate, CCur, etc. |
| |
|
|
| |
|
|
|
Date/Time Functions Part 1
More with Now, Date, Time Functions
Query Data in a Variety of Date Ranges
Date & Time Mathematics
Two Digit Year "2030" Problem
List of Upcoming Birthdays
Aged Accounts Receivable Report
Display Times as 8:30, 8.5, 8h 30m
Timesheets Spanning Midnight
Calculating Someone's Approximate Age |
| |
|
|
| |
|
|
|
Date/Time Functions Part 2
Break Apart with Day, Month, Year
Get Day of the Week with WeekDay
DateAdd - 2 months from today?
DateDiff - How long until loan paid?
DatePart - Is order from last quarter?
DateSerial - Find last day of month
Display Ordinals: 1st, 2nd, 3rd, etc.
Calculate someone's exact age
List of birthdays for next month |
| |
|
|
| |
|
|
|
Aggregate Functions
Sum, Avg, Count, Max, Min, First, Last
Domain Aggregate Functions
Lookup Functions
DLookup, DSum, DCount, DAvg
DMax, DMin, DFirst, DLast
Calculate Average Daily Sales
Create Your Own Custom Counter
Display Running Sums (Balances)
Excel VLOOKUP Replacement |
| |
|
|
| |
|
|
|
Financial Functions
Simple v. Compound Interest
Loan Payment Calculator: PMT
Find Future Value of Investment: FV
Calculate Return On Investment: RATE
Years Until I'm a Millionaire: NPER
How Much Home Can I Afford: PV
Saving For Kids College Fund Calculator
Loan Amortization Basics
Calculating Principal and Interest Parts |
| |
|
|
| |
|
|
|
Charting Basics
Chart Components
Formatting Charts
Multiple Data Series Charts
3D Charts
Forecasting with Trendlines
Pie Charts
Creating a Chart Dashboard
Chart Reports
Lots more |
| |
|
|
| |
|
|
|
Report Grouping Headers
Display a List of All Reports in Database
Generate a Custom List of Reports
Report List Form, Click to Open
Subqueries, Rank, Median, Mode
Insert Blank Mailing Labels at Top
Intro to Macro Programming
GoToRecord, SetValue Commands
InputBox, RunMenuCommand
Lots more |
|
| |
