Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
Home > Old Access Index > Expert < Beginner | Advanced >
 
Access Index - Expert

Access Expert 1

 

00. Intro (7:13)

01. Relational Database Concepts (17:06)
What is a Relational Database
Why are Relationships Important
Flat File Databases
Key Fields
Primary Key
Foreign Key
Types of Relationships
One-to-one Relationships
One-to-many Relationships
Many-to-one Relationships
Many-to-many Relationships
Junction Table
Normalization

02. Relational Tables (14:53)
Making our contact management relational
Inserting a CustomerID in ContactT
Removing duplicated fields
Setup a Foreign Key
Moving fields in table design
Fixing Lead Sources
Fixing Shipping Methods

03. Relational Queries (14:04)
Add CustomerT to ContactQ
Ad Hoc Query Relationships
Automatic Join Line
Delete Relationship
Manually Create Relationship
Asterisk from One Table
Join Missing Customers
Join Types
Left Join / Outer Join
Joined fields from both tables are equal
Show All Records from CustomerT
Only those records from LeadSourceT
Where the joined fields are equal
Fix ContactR Report

04. Relational Combo Boxes (19:34)
Add Combo Box to Contact Form
Combo Box Based on Table
Combo Box Based on Query
Wizard is Slightly Different
Fix Lead Source Combo
Fix Shipping Combo

05. Review (04:50)

 

Access Expert 2

 

00. Intro (7:04)

01. Normalization (21:06)
What is Normalization
First Normal Form (1NF)
Flat Tables
Atomic Fields
Repeating Groups of Data
Entities (Entity)
Second Normal Form (2NF)
Update Anomalies
Third Normal Form (3NF)
Remove Derived Fields
Calculated Values
Denormalization
Improve Performance
Storing History Data

02. Explicit Relationships (14:00)
Global Relationships
Relationships Window
Add Tables
Edit Relationships Window
SubDatasheet
Subtable
Orphaned Child Records
Edit Relationship
Referential Integrity
Unable to Delete Parent

03. Cascade Update & Delete (13:16)
Cascade Updates
Why you should never need them
Cascade Deletes
Automatic Delete of Children
Backup Your Data!
Can't Delete Tables in Relationships
Arrange Tables in Relationship Window
Hide Tables
Delete Relationships
Relationships Report
Object Dependencies
Subdatasheet Table Properties
Subdatasheet Expanded
Subdatasheet Height
Subdatasheet Name
Link Child Fields
Link Master Fields

04. Values Across Forms (23:13)
Find Contacts for Current Customer
Command Button
Open Form
Find Specific Data To Display
Save Form Position
Set Values Between Forms
Forms Collection
Forms!FormName!FieldName
Default Value
Value of a Combo Box
Combo Box Columns
Bigger Notes Field in Form Footer
Move Combo Box to Form Footer
TabStop Property
Brief Intro to Expression Builder
Dirty Records Refresh Problem

05. Review (6:11)

 

Access Expert 3

 

00. Introduction (7:38)

01. SQL Primer (11:39)
Combo Box Row Source
SELECT FROM
ORDER BY
WHERE
Change Combo Sort
Change Combo Field Order
Record Source for Form

02. Callbacks (19:26)
Modify ContactT
Default Value for Dates
Today's Date
Tomorrow's Date
Tomorrow at Noon
Tomorrow at 9 AM
Modify Contact Form
Custom Date Format
ddd mm/dd/yyyy hh:nn am/pm
Callback Query
Sort by Date
Callback Form
Add button to Main Menu
Add customer name to list
Button to open Customer Form

03. Contact Subform (16:27)
What is a Subform
Master and Sub Forms
Parent and Child Forms
Copy and modify Contact Subform
SQL Statement as Record Source
Sort by Date Reverse Order
SELECT ORDER BY DESC
Subform Object Control
Click and Drag to create Subform
Subform Object Properties
Subform Form Properties
Access Autoform with Subform
Link Master Fields
Link Child Fields

04. Form Footer Totals (23:59)
Product Category Table
Product Table
ProductSubformF
ProductCategoryF
Aggregate Functions
SUM AVG MAX MIN COUNT
Modify Embedded Macro
Change Form Name in Macro
Moving to Access 2013

05. Review (6:41)

 

Access Expert 4

 

00. Intro (7:37)

01. Extended Info Tables (19:26)
Create CustomerExtendedT
ShortText and LongText Fields in 2013
Yes/No NULL Value
Password Input Mask
Family Member Table
Relative Type Table
Force One-to-One Relationship
Indexed No Duplicates

02. Extended Customer Form 1 (23:55)
Create Extended Customer Form
Option Group
Yes as -1, No as 0
Unspecified option
Change y/n to number
Create List Box from Scratch
Create Toggle Buttons
Manipulate Toggle Colors
Lighter Darker 50%
Hover Color
Pressed Color

03. Extended Customer Form 2 (26:05)
Command Button Open Ext Form
Default Value
Forms!CustomerF!CustomerID
Locked Property
Change Form Properties
Modal, Popup
Navigation Buttons
Record Selectors
Caption & Trick
Tab Controls
Image Control
Save Path Filename in Table
Set Image Control Source
Customer With Image Query
Display Image on Report
Customer Labels with Image

04. Family Member Form (14:15)
Description Indexed No Duplicates
Relative Type Helper Form
SQL Recordsource
Family Member Form
Relative Type Combo Box
Link Master Fields
Link Child Fields
List Items Edit Form

05. Toolbox Control Overview (11:20)
Hyperlink Control
Label with Hyperlink Address
Show File Extensions
Brief Discussion Of...
Web Browser Control
Page Break Control
Chart Control
Line & Rectangle Controls
Attachment Control
ActiveX Controls

06. Review (5:12)

 

Access Expert 5

 

0. Intro (06:52)

1. Contact Letter Query (9:51)
Make Notes Field Rich Text
Set fields to Rich Text
Open Report to Specific Record
Query Criteria Forms!FormName!Field
Run Query Without Form Open
Enter Parameter Value Prompt

2. Contact Letter Report (15:16)
Format of a Printed Letter
Create Blank Report
Bring in Contact Query Fields
Remove Borders
Long Date Format
Create FullName Field in Report
String Concatenation in Text Box
Can Grow, Can Shrink
Text Box and Section Grow / Shrink
Label for Return Address
SHIFT-ENTER for New Line
Paper Size
Margins
Put Logo on for Letterhead
Line and Web Address in Page Footer

3. Print Letter Button (11:45)
Command Button
Print Preview Report
Dirty Records
Refresh Data Before Opening Report
Edit Embedded Macro
Build Event
OpenReport Command
Add New Action
RefreshRecord Command
Change Order of Macro Commands
Match Size of Text Box with Letter
Page Numbering
Page N of M

4. Export or Email Letter (18:15)
Export to Excel
Export as Text File TXT
Export as PDF or XPS
Send as Email Attachment w Outlook
Export as Word Document
Export as HTML Document
Email Report Command Button
EMailDatabaseObject Command
Object Type
Object Name
Output Format
Anatomy of a Hyperlink Field
HyperlinkPart Function

5. Customizing the Ribbon (9:06)
Using ALT Keys
Minimize the Ribbon
Collapsing the Ribbon
Customize Quick Access Toolbar
Add to Quick Access Toolbar
Access Options
Customize Ribbon
Popular Commands
Commands Not in the Ribbon
New Custom Tab
New Custom Group

6. Review (5:54)

 

Access Expert 6

 

00. Intro (7:10)

01. Collection Letters 1 (11:32)
Create OrderT Table
Create OrderCollectionLetterQ

02. Collection Letters 2 (12:17)
Copy current contact letter
OrderCollectionLetterR
Change record source
Detail section properties
Force New Page After Section
Turn off Alternate Background Color
Add order info fields to letter

03. Collection Letters 3 (13:29)
LetterT Table
LetterF Form
Enter multiple letters
Text on report set by form
BeginDate and EndDate for query
Can't edit with Date() in Control Source

04. Collection Letters 4 (8:55)
Put LetterF on Main Menu
Change date range based on letter
BeginDaysAgo, EndDaysAgo in LetterT
Set BeginDate and EndDate Automatically

05. Generic Letters (19:19)
Send letters by various criteria
Letter to Active customers
AllowEdits vs. Locked Field
CustomerActiveQ Query
LetterActiveR Report
Letters by Lead Source
LeadSourceCombo
LettersByLeadSourceQ

06. Review (5:21)

 

Access Expert 7

 

00. Intro (8:08)

01. Lookup Values in Tables (17:46)
Add Group Field to CustomerT
Lookup Data Type
I will type in the values
Limit to List
Cons of Lookups in Tables
NOT Supported by SQL Server
Allow Value List Edits
Allow Multiple Values
Row Source Item List
Semicolon Delimited Items
Two column text value list
Bound Column
Column Count
Lookup from another table
SQL Statement directly in table
Multi Valued Fields
Allow Multiple Values
Cons of MultiValue Lists
Delete Relationship to GroupT

02. Many to Many Relationships 1 (13:10)
Multiple groups per customer
Many-to-Many
Cross-reference table
Junction table
Customers to Groups
Vendors to Products
GroupXCustomerT
Group Subform on Customer Form

03. Many to Many Relationships 2 (6:25)
Group Form
Show Customers in Group
Copy CustomerGroupSubF
Create GroupCustomerSubF
SHIFT-ENTER line break in label
Groups button on Main Menu

04. Letters By Group (16:36)
IncludeInMailing Field in GroupT
GroupMailingQ
LettersByGroupR
GroupSubF
RefreshRecord in Macro
Duplicate customers in mailing
Quick overview of Aggregate Queries
Group By
Min Value

05. Post Cards (12:02)
Create Report Design
Shape Outline Transparent
Landscape v Portrait
Page Setup Dialog
Number of Columns
Row Spacing
Column Spacing
Column Size
Same as Detail
Down, then Across
Across, then Down
Margins
Resizing with the Rulerbar
Button from Letter Form

06. Vendors to Products (17:16)
Build ProductF Form
Vendor Table
VendorXProductT Junction Table
UnitCost
DateUpdated
Profit Calculation

07. Review (5:10)

 

Access Expert 8

 

00. Intro (8:22)

01. Products to Vendors (24:01)
Homework Review
Button to Open Product Form
VendorF Form
VendorProductSubF
Button to Open Vendor from Product
Button to Open Product from Vendor
Give Names to VendorCombo, ProductCombo
Query to add UnitPrice to Junction
VendorXProductWithProfitQ
Add Price and Profit to Vendor SubF

02. Order Details Table (11:42)
Create OrderDetailT
Delete AmountDue from OrderT
Possibility of breaking stuff!
Create OrderDetailQ
Pull in data from ProductT

03. Calculated Query Fields (12:29)
Calculate ExtPrice
String Concatenation Reviewed
Assignment Operators
* / + - \ ^ MOD
Integer Division
Modulus
Order of Operations
PEMDAS
Comparison Operators
< > <= >= <> =
AND OR NOT XOR
-1 or 0 for True False
Boolean Values
Format Property in Query Column
Format True/False
Format Currency

04. Sales Tax (16:32)
IF THEN Statements
IIF Function
Function Review
SUM AVG MAX MIN COUNT
DATE NOW TRIM
SalesTax IIF Function
ROUND Function
Rounding Numbers
Bankers Rounding
Traditional v Bankers Rounding
Nested Functions
Nesting Functions
Fractional Penny Problem
INT Function Round Down

05. Order Detail Subform (14:44)
Create OrderDetailF Subform
Product Combo Box
Lock UnitPrice Field
Order Relationships
Customers to Orders
Do NOT Cascade Delete
Ordsrs to Details
DO Cascade Delete

06. Order Form (22:18)
Create OrderF
Customer Combo Box
Show Name if Company Null
Is Null vs IsNull()
Add OrderDetailSubF
Auto Default CustomerID
Auto Default IsTaxable
Form Footer Calculations
Sum of ExtPrice
Sum of SalesTax
Grand Total

07. Review (5:06)

 

Access Expert 9

 

00. Intro (8:39)

01. Customer Discount Rate (20:26)
Discount Rate for Orders
Discount Rate for Order Details
Different Validation Rules
Recalculate Sales Tax
Tab Cycle Property
LineTotal

02. Multiple Sales Tax Rates (17:42)
SalesTaxRate for Customers
SalesTaxRate for Orders
SalesTaxRate for Order Details
Values Propagate Down Forms
Disable Close Button
Disable Control Box
System Default Values Table

03. Non Taxable Products (12:01)
IsTaxable for Products
Show IsTaxable in Product Combo
SQL ALIAS
IF Product Tax Exempt
Another IIF Example

04. Printable Invoice 1 (20:27)
Order Detail Subreport
Report Header / Footer
Page Header / Footer
Order Info with Customer Data
OrderQ
Customer Bill To Info
Insert Subreport

05. Printable Invoice 2 (19:58)
Company Info in Header
Missing Company Name Gap
Can Shrink Problem
Adjacent Control Problem
Overlapping Controls
TRIM Function
Open Report to Specific Record
Query Criteria
Company Return Address Info

06. Review (6:08)

 

Access Expert 10

 

00. Intro (7:02)

01. Quote or Invoice (28:09)
Add My Company Info to Defaults
Company Logo
Size to Tallest
Paid or Unpaid Text on Invoice
Refresh Dirty Record
IsQuotation Field on Order Form
Quotation or Invoice Text
Nested IIF Functions
Hide Paid Text if Quotation

02. DLOOKUP Function (12:10)
Move System Defaults to Own Form
DLOOKUP Field, Table
Get SalesTaxRate from DefaultT
Edit Invoice to use DLOOKUP

03. More with DLOOKUP (25:35)
Sales Rep for Customer
SalesRepID
ServiceTechID
Related Fields with Different Names
Sales Rep Combo Box
Service Tech Combo Box
SalesRepID on Order Form
Display Sales Rep's Phone Number
Criteria Format
"EmployeeID=" & SalesRepCombo
@@@-@@@-@@@ Phone Number Format
Strings Inside Criteria
Double Double Quotes ""
Dates in Criteria need # signs
AND Condition in Criteria
Add Rep Name and Phone to Invoice

04. Calculated Table Fields (8:31)
Expressions in Tables vs Queries
Problems with Calculated Table Fields
When to use them
Example: TimeToShip in Days

05. Review (5:35)

 

Access Expert 11

 

00. Intro (8:38)

01. Simple Aggregate Queries (13:13)
Aggregate Query
Totals Query
Summary Query
Grouping Query
What they are
Popular Aggregate Functions
SUM, AVG, COUNT
FIRST, LAST
MAX, MIN
Statistical Analysis
STDEV, STDEVP
VAR, VARP
Covered in future class
SUM in Form Footer Total
SUM in Table Footer (last row)
Totals Button
Simple SUM of all credit limits
Group By State
Default sort on Group By field
Sort by Credit Limit descending
Add criteria Is Not Null
Notice Field Name is Changed
SumOfCreditLimit
AvgOfCreditLimit
One query with SUM, AVG, MAX, MIN
ALIAS field names
Count of customers from each state
Only NON-NULL values are counted
Count ID field to include NULLs
Simple Query Wizard
Aggregate Options in Wizard
Summary Information

02. Complex Query Criteria (7:44)
Credit Limit by State After 1999
Brief overview of date functions
MONTH, DAY, YEAR Functions
HOUR, MINUTE, SECOND Function
Break into Two Queries
WHERE Field in Total Row

03. Sales Totals by Month (10:50)
Format to show "yyyy mm"
Format Property in Query Field
Format() Function
Week of Year ww

04. Lowest Product Cost (12:46)
MIN Cost for each Product
DLOOKUP Vendor ID at Lowest Cost
DLOOKUP Vendor Name, Unit Price
Calculate Most Profitable Products

05. Last Customer Contact (6:36)
MAX of Contact Date
DLOOKUP Contact Notes
Dealing with NULL Values
IIF Function Review

06. Employee Work Log (26:02)
Create Work Log Table
Time In, Time Out
Timeclock
Difference Between Two Times
Calculate Hours Worked
Caution with Dates and BETWEEN
BETWEEN #1/1# and #1/2#
Does NOT Include #1/2 5pm#
>=Start AND CDATE() Function Brief Mention
Work Log Lookup Form
Employee Combo Box
Command Button Run Query
Forms!WorkLogLookupF!StartDate
Homework for Next Class

07. Miscellaneous (16:13)
Expression Option
DSUM Sales Last 30 Days
Refresh Form Data Button
Can't Refresh Unbound Form

08. Review (5:52)

 

Access Expert 12

 

00. Intro (8:01)

01. Order List Form (22:57)
Create Order List Aggregate Query
IIF to display PAID / UNPAID
IIF to display QUOTE / INVOICE
OrderListQ
AllowEdits
AllowDeletions
AllowAdditions
Sum of Order Total in Footer
Sort by Order Date Descending
Refresh Form Data Button Doesn't Work
Refresh vs Requery
Refresh vs Refresh All Buttons
Button Build Event
RunMenuCommand Refresh
Requery Macro Command

02. Work Log Entry Form (35:28)
Create a Button Without the Wizard
Copy Existing Button
Edit Embedded Macro Form Name
Copy Button for Query
Resize to Tallest
Create Work Log Form
Employee Combo Box
Tab Order
Bound Combo in Form Footer
Unbound Combo in Form Footer
Combo Default Value
DefaultEmployeeCombo
Default Date Textbox
TodaysDate
=[TodaysDate]+#9 AM#
Populate Defaults with Unbound Fields
Work Log for Current Employee
Add to Main Menu
Track Employee Activities
Activity Table
Add Activity Combo to Work Log

03. Fix Collection Letters (5:57)
Parameter Prompt from Missing Field
Update Order Collecton Letter Query
Aggregate Total for Amount Due

04. Report Grouping 1 (15:08)
Customers Grouped by State
Group & Sort
Add a Group
With a Header Section
With a Footer Section
With Totals
Group Footer Totals
Horizontal line
Line thickness
Showing a Value if Header IsNull
IIF IsNull
Display [None]
Circular Reference

05. Report Grouping 2 (17:56)
Add Credit Limit
SUM of Credit Limit
Grand Total
No Aggregate Functions in Page Footer
Page of Pages
Page Footer Below Report Footer
Section Visible Property
Hide Sections
Do not keep group together on one page
Keep whole group together on one page
Keep header and first record together
With title label - BUG
Force New Page After Section
Multiple Grouping Levels
No Country display United States

06. Employee Work Log Report (16:56)
Work Log Report
Group by Employee
Subgroup by Week of the Year
Access has no Week or WeekNum Function
DatePart Function
Calculate First Day of the Week
Week Starting Date
SUM of Hours Worked by Week
Total Hours for Employee

07. Number & Currency Grouping (17:55)
Show Sales Grouped by Rep
Subgroup by Month
Change to Numeric Grouping
Orders Grouped Every $500
Range of Values for Header
$0 to $499.99
Integer Division \
Format(X,"Currency")

08. Miscellaneous (7:23)
Group by Expression
Use a Query Expression Instead
Simple Report Wizard
Homework!

09. Review (5:21)

 

Access Expert 13

 

00. Intro (7:20)

01. Open Report WHERE Condition (28:04)
Buttons on Main Menu for Reports
Replace Invoice with Grouped Report
Same field name on two tables
OrderT.F1 and OrderDetailT.F1
OrderID Header Section
Open Report to Specific Multiple Records
Print Multiple Invoices
Macro WHERE Conditions
Print All Invoices for a Customer
Print All Invoices Between Dates

02. Types of Action Queries (5:49)
What are Action Queries
Update Query
Append Query
Delete Query
Make-Table Query
Backup your data!
How to backup your database file
Online backup recommendations
Backing up a table

03. Update Queries, Part 1 (14:48)
Update Queries allow Complex Criteria
Change Select to Update Query
Update To Property
RUN vs Datasheet View Buttons
Preview Records to be Updated
Running the Update Query
Multiple Criteria
Change "XYZ Corp" in "PA"
Save Query
Nested IIF in Update Query
Assign Reps Based on Territory

04. Update Queries, Part 2 (10:26)
Capitalize States
UCASE() Function
Can't Delete a Table with Relationships
Manually Delete Relationships
Delete and Rename Backup Table
Restore Relationships
LCASE() Function
STRCONV() Function for Proper Case
Exceptions: Don't change McDonald

05. Update Queries, Part 3 (9:26)
Add 5% to all Product Prices
UnitPrice = UnitPrice * 1.05
Update all Paid Orders as Shipped

06. Review (6:03)

 

Access Expert 14

 

00. Intro (9:13)

01. Scrubbing Data (15:49)
Cleaning Data with Update Query
Inconsistent Data Formats
Fax Numbers with Different Formats
Clean them to look uniform
Backup Your Data!
REPLACE() Function
Replace one string with another
Watch Field Names Carefully
Make sure you use [Brackets]
Null values can cause problems
LEN() Function
Length of String
Show FaxNumbers 7 digits long

02. Change Email Domains (12:39)
Hyperlink fields are not simple text
CSTR() Function
Convert to String
HyperlinkPart() Function
Change Domain Name
amicron.com change to 599cd.com
NZ() Function
Null to Zero

03. Separate First and Last Names (19:29)
Create Person Table
Put First and Last Names Together
INSTR() Function
Determine the position of the SPACE char
LEFT() Function
RIGHT() Function
MID() Function (Discussion)
Check for Prefix, Suffix, Middle Initial

04. Fix Non-Relational Table (9:51)
Employees and Departments
Turn Department Names into IDs
Query Join on Text Values
Update to IDs from other table

05. Daily Student Attendance (18:34)
Create Student Table
Attendance Table
Make Append Query
Append To Table Name
Current Database
Another Database (Discussion)
Only Students from One Class
Criteria in Append Query

06. Review (8:14)

 

Access Expert 15

 

00. Introduction (8:27)

01. Student Attendance 1 (20:12)
Types of Different Classes
ClassTypeT
Individual Class Sessions
ClassT
ClassQ with Type Name
Track Customers in Each Class
ClassXCustomerT
Class Form with Enrollment Subform
ClassF
ClassXCustomerSubF
Add Multiple Students to Class
Each Company Sends Multiple Students

02. Student Attendance 2 (31:12)
Create Attendance Table
AttendanceT
Master Attendance Form
Student Subform
Modal Popup
Default Values from Other Forms
AttendanceQ With Supporting Data
Left Joins
Create Attendance Subform
Data Entry Property
Attendance Append Query
Add Static Date Value to Append Query
CDate Function

03. Student Attendance 3 (34:59)
Create Button to Run Append Query
SetWarnings OFF
Turn on Potentially Unsafe Actions
Show All Actions
Requery
Short Date Format for Date Picker
Show Attendance for Any Date
Attendance Report By Class By Date
Sorting & Grouping
Attendance Report by Student then Date

04. Contact Archive (15:37)
Backup Contact Table
Create Contact Archive Table
ContactArchiveAppendQ
Remove Indexing Primary Key from Archive
Change AutoNumbers to Numbers

05. Review (6:31)

 

Access Expert 16

 

00. Intro (8:25)

01. Macro to Run Multiple Queries (9:21)
Command Button Wizard
Open Query
Delete Queries Not Listed
Archive Contacts Button
Macro Builder
OpenQuery Command
Add New Action
Show All Actions
SetWarnings On/Off
MessageBox Command (MsgBox)

02. Delete Query Multiple Tables (8:56)
Referential Integrity
Delete Queries and Cascade Deletes
Delete Contacts from Customers in CA
Two Queries Needed
Customer By State Query

03. Make Table Queries (16:05)
Create a Customer Mailing List Table
Make Table Query in Same Database
Create a new blank database ACCDB file
Make Table Query into a Different Database
Backup Customer Table
Multi-Valued Fields Not Allowed
Long Text with Append Only Property On
Automated Backup of Contact Table
Include in Command Button Macro
OpenQuery Command

04. Multiple Parent Mailing List (18:25)
Create Student Table with 2 Parents
Include In Mailings Field
Make Table Parent 1
Append Parent 2
Show Another Example Relational
Use a Query with a Join

05. UNION Query (10:30)
Merge Contacts with Archive
Don't use the Union button
UNION SQL statement
Homework Assignment

06. Review (7:25)

 

Access Expert 17

 

00. Intro (9:05)

01. Archive Old Unpaid Orders 1 (13:30)
Add ToBeArchived y/n Field
Create ToBeArchivedUpdateQ Query
Button to Archive Old Orders on Main Menu
Database Level Macro Primer
Macro to Open Main Menu
Putting Macros on Quick Access Toolbar

02. Archive Old Unpaid Orders 2 (21:22)
Make OrderListF Show All Order
Add ToBeArchived to Order List Form
TBA Tool Tip
To Be Archived checkbox on Order Form
Macro WHERE Condition
ToBeArchived=TRUE
Create OrderArchiveT
Create OrderDetailArchiveT
Set Autonumbers to Long Int
Append Orders
Create AppendOrderDetailArchiveQ
Create AppendOrderArchiveQ
Duplicate output destination
Create OrderToBeArchivedQ to avoid error

03. Archive Old Unpaid Orders 3 (21:27)
Backup tables with MakeTable query
MakeCustomerBackupQ
MakeOrderBackupQ
MakeOrderDetailBackupQ
Calculated columns are not allowed in SELECT INTO statements
Another reason I don't like calculated table fields
Remove DaysToShip from OrderT
Update customer record "Order Writeoff"
UpdateCustomerOrderWriteoffQ
Append "Order Writeoff" to ContactT
Put writeoff notice in contact table
Add OrderID to writeoff notice
AppendOrderWriteoffQ
Update the order description with "WRITEOFF!"
UpdateOrderWriteoffQ

04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
DeleteArchivedOrderDetailQ
DeleteArchivedOrderQ
Copy and modify append queries
Delete FROM and WHERE
Make button on TBA form to perform archive
Copy button with macro in it
CloseWindow macro command
Macro commmand to Close Form
UNION query to show orders and archive together
SQL View
Fields must match exact with UNION and * all fields

05. Crosstab Queries (13:16)
What is a Crosstab Query?
Similar to an Excel PivotTable
Show example of a PivotTable
Add some new orders
Edit OrderListQ Add State Field
Create Crosstab Query
Crosstab Query Wizard
Built Crosstab Query Manually
Set up Helper Query
Orders by Month by State
OrderByMonthByStateCrosstabQ

06. Review (5:38)

 

Access Expert 18

 

00. Intro (6:26)

01. Crosstab Query Parameters (16:45)
Sales by Sales Rep by Month Crosstab
Fixed Column Headings
Manual Row Headers
Multiple Row Headings OK
Multiple Column Headings NOT OK
Criteria in Crosstab Queries
Date Criteria in the form yyyy-mm
Declaring Explicit Query Parameters
Declare Parameter Even If Form Field

02. Sales Rep by Quarter Crosstab (06:11)
Query Wizard Date Groupings
Total Row Headings
Count Row Headings
Crosstabs Used With Other Queries

03. Partition Function (7:29)
Tiers of Products
PARTITION() Parameters
Group Products into Price Brackets
Count of Products in Each Tier
Product Category Row Header
Product Tier Column Header
Value is Count of Products in Tier

04. Find Duplicates Query (21:19)
Create Find Duplicates with Wizard
IN() Function Review
SQL HAVING Clause, Brief Discussion Of
Manually Create Find Duplicates Query
Use an Aggregate Query Count > 1
Find Duplicates Multiple Fields
Unique Values
Unique Records
Field List Properties
Query Properties
Field Properties
Output All Fields
Recordset Type Dynaset vs. Snapshot
Prevent Editing of Records in Query
Order By Property
Filter Query Property
Filter On Load
Order By On Load
What is an ODBC Database
ODBC Timeout
Max Records
Record Locks, Brief Introduction
Record Locking
All Records
Edited Records
Orientation Right-to-Left, Left-to-Right
Subdatasheet Height
Subdatasheet Expanded

05. Find Unmatched Query (11:36)
Use Wizard
Create Unmatched Query Manually
Left Outer Join Important
Is Null
Cartesian Product
Cross Product
Softball Schedule
Each Team Plays Each Other Team Once

06. Review (5:23)

 

Access Expert 19

 

00. Intro (6:15)

01. Conditional Formatting (28:13)
Format large orders green
Conditional Formatting Rules Manager
New Formatting Rule
Check values in the current record or use an expression
Field Value Is
Greater than, Less than, etc.
Highlight orders over $500 in green
Add Views to Quick Access Toolbar
Conflicting Rules
Order of Applied Rules
Using Functions like Date() in Conditions
Formatting Date Values
Formatting Text Values Exact
Find Text Inside of Field
Expression Is
InStr Function
Format Customers with "Inc" in name
Apply to Multiple Fields at Once
Format Entire Row
Format if Field Has Focus
Disable Changes if Order Marked Paid
Conditional Format Subform Value
Disable Editing Products on Order if Paid
Compare to Other Records
Data Bars
Lowest, Highest value
Numbers, Percentages
Max 50 Conditions

02. Exporting to Excel (16:37)
Reasons to Export Data
Export Table to Excel
Different Versions of Excel Data
Export data with formatting and layout
Open the destination file after the export
Export selected records
Copy and Paste from Access to Excel
Copy Without Column Headers (Field Names)
Add an Export Checkbox to Table
Export Specific Records Query
Hide the Export Field
Save Export Steps
Create an Outlook Task with Reminder
Saved Exports
Manage Data Tasks
Run Saved Export Routine

03. Exporting to Word (15:05)
Export Mailing Labels
Export RTF File to Word
Mail Merge Access Data to Word Document
Word Merge
Microsoft Word Mail Merge Wizard
Link your data to an existing Microsoft Word document
Create a new document and then link the data to it
Mail Merge Wizard
Edit Recipient List in Word
Insert Address Block
Insert Merge Field
Preview Your Letters
Word Doc Automatically Updates from Table
Copy and Paste Table Data to Word

04. Exporting Text Files (10:14)
Export Text File
Export Windows Text
MS-DOS Text
Notepad Application
Basic Access Text Export is Garbage
Export Without Formatting
Export Text Wizard
Fixed Width vs. Delimited Text
Changing Width of Field
Field Delimiter
Text Qualifier
Date Order
Date Delimiter
Time Delimiter
Four Digit Years
Leading Zeros in Dates
Decimal Symbol
Save Export Specification
Include Field Names in First Row

05. Export Misc (18:23)
Export to XML
Export to Another Access Database
Database must be closed
Access needs exclusive read/write
Queries Export as Queries NOT Tables
Copy Multiple Tables
Export to Sharepoint, Brief Discussion
Export to ODBC, Brief Discussion
Export to SQL Server, Brief Discussion
Export to HTML Document
Formatted v. Unformatted HTML
Coming Up

06. Review (5:32)

 

Access Expert 20

 

00. Intro (5:43)

01. Send Bulk Email via Word (29:13)
Query for Customers to Email
Active Customers with Email Addresses
CustomerForEmailExportQ
External Data > Export > Word Merge
Create a New Document
Mail Merge Wizard
Use the Current Document
Use an Existing List
Edit Recipient List
Format Document
Insert Pictures
Preview your Email Messages
Complete the Merge
Merge to Email
To, Subject, Mail Format HTML
Check Names Error Message
Outlook Cannot Handle Hyperlink Fields
Separate Email Address from Hyperlink
HyperlinkPart Function
Word: Undefined Function HyperlinkPart
Make Table Query for Email Table
CustomerForEmailExportT
Find Data Source
Step by Step Mail Merge Wizard
Finish & Merge
Email Active Customers Button
Macro: Show All Actions
SetWarnings On/Off
OpenTable Command
RunMenuCommand WordMailMerge
Switch to Email Messages

02. Importing & Linking from Excel (21:27)
Copy and Paste Cells to New Table
Does first row contain column headings
Importing Proper Data Types
External Data > Import & Link > Excel
Import Excel Sheet Data
Import Spreadsheet Wizard
Field Name, Data Type, Indexed, Skip
Let Access add primary key
Save Import Steps
F5 to Refresh Navigation Pane
Saved Imports > Run
Import Errors
Append a copy of the records to the table
Linking to an Excel Spreadsheet
Link to the data by creating linked table

03. Importing Text & HTML (16:18)
Discussion of Different Import Features
Linked Table Manager, Brief Discussion
ODBC Database, Brief Discussion
Sharepoint List, Brief Discussion
Data Services, Brief Discussion
Importing Text Files
Advanced Import Specification
Importing HTML Documents
Importing HTML from a Web Page Table
Saving a Web Page as HTML

04. Exporting & Importing XML (14:43)
What is XML
Why is XML Better than Text or HTML
Exporting a Table in XML Data Only
Exporting Data with Schema XSD File
Exporting XML with Embedded Schema Data

05. Linking to Outlook Folders (11:21)
Link to Outlook Inbox
Read and Display Email in Access
Editing Email in Access
Linking to Outlook Contacts
Displaying Contacts in Access
Editing Contact Info in Access

06. Review (10:38)

 

Access Expert 21

 

00. Intro (7:14)

01. Import & Link Access Objects (14:45)
Import by Click-and-Drag
Import by External Data > Access
Browse to Database Folder
Import Objects Dialog
Import Options
Import Table Relationships
Import Menus and Toolbars
Import/Export Specs
Import Nav Pane Groups
Import All Images and Themes
Table Definition AND Data
Table Definition Only
Import Queries as Tables
Importing vs. Linking
Link to Customer Table
Custom Forms for Front End

02. Multi-User Database Theory (14:53)
Sharing a Database
Why Split Your Database
Front End vs. Back End Databases
Pros and Cons of Split Databases

03. Splitting Your Database 1 (20:38)
Compact & Repair First
Backup Your Data
Database Tools > Move Data > Access
Database Splitter Wizard
The database engine could not lock table
Already in use by another person
Notice Linked Tables
Set up Shared Folder
Linked Table Manager
Rename or Move Backend Database
Refresh Location of Backend Links
Setting up a Shared Folder
Folder Properties > Sharing
Assign Shared Folder Name
Setup Folder Permissions
Select Users or Group
UNC Path
Mapping a Drive Letter

04. Splitting Your Database 2 (21:15)
Storing Images on Server Share
Creating a 2nd Back End Database
Move a Secure Table to Different File
Creating a 2nd Front End Database
Custom Databases based on User Role
Distribute Encrypted Front End
Users Cannot Make Design Changes
File > Save As > Make ACCDE
Trusted Location - Shared Folder

05. Record Locking (28:42)
How Default Record Locking Works
Write Conflict Error Message
Save Record, Drop Changes
Copy to Clipboard
Form Properties > Record Locks
No Locks, All Records, Edited Record
What is an LACCDB file?
Database Client Settings
Page Level vs. Record Level Locking
Default Open Mode: Shared / Exclusive
File Open for Exclusive One Time Use
Default Record Locking
Open databases by using record-level locking
Changes take effect when first user opens db
Delete LACCDB file if record still locked

06. Review (6:15)

 

Access Expert 22

 

00. Intro (9:09)

01. Fixing Non-Relational Data (24:25)
Create Bad Spreadsheet
Import Sheet into Access
Aggregate Query for Unique Customer List
Customer Query with Most Recent Info
DLOOKUP Most Recent Address & Phone
Remove Customer Data from OrderT
Add CustomerID Foreign Key
Query to Change CustomerID
Delete Automatic CustomerID Join
Create New Join on Customer Name
Make Table Query to Create CustomerT

02. Update Vendor Pricing (41:41)
Price Sheet Changes from Vendor
Create Spreadsheet with Product Costs
Import Data into Access
Link Our Data with Vendor Data
Joined Query is Too Complex for Update
Make a Simpler Query
Discontinued Products have Null Price
Create Macro to Automate Import of Data
Make Button to Run Import
ImportExportSpreadsheet
RunSavedImportExport

03. Fix the Order Entry System (42:21)
Fixing the Order Entry System

Current DB alters orders on UnitPrice change
Add ProductName, UnitPrice to OrderDetailT
Fix the OrderDetailQ
Unbind Product Combo Box, Move to Footer
Add ProductID, ProductName
Lock ProductID
Unlock UnitPrice
Create Embedded Button Macro
Build Event > Macro Builder
IF Macro Command
MessageBox Macro Command
StopMacro Macro Command
GoToControl Macro Command
GoToRecord Macro Command
Show All Commands Button
Potentially Unsafe Macro Commands
SetValue Macro Command
Set SalesTaxRate with IF Statement

04. Review (5:28)

 

Access Expert 23

 

00. Introduction (7:58)

01. Remote Copy For Data Entry (36:44)
Copy PC Resale Main Database
Customize for Remote Use
Lock Customer Form
Don't Allow Edits, Deletions, Additions
Only Allow Additions on Contact Form
IsRemote Yes/No Field
Link to Main Database Tables
Not Connected to Network Error Message
Append New Records to Server
Delete Data in Remote Database
Copy All Records from Server to Remote
Macro to Perform Synchronization

02. Import Paypal Transactions (25:37)
What is Paypal
Download Paypal History File
Import Paypal Data Into Access
Set up my Transaction Table
Prevent Duplicate Records

03. Composite Keys (15:44)
Prevent Duplicate Values Multiple Fields
Create Store Locations Table
Do Not Want Two Stores in Same City
Indexed, No Duplicates
Indexes Dialog Box
Create the Composite Index
Also Called a Compound Index
Make Sure Store Doesn't Report Sales Twice
Create StoreSalesT
Prevent Duplicate Store Sales Figures
Allowing Duplicate Values
Find Duplicates Query Wizard
SQL Statement Explained Briefly

04. Transfer Multiple Tables XML (7:23)
Send Multiple Tables with Related Data
Exporting Customers with Orders
Export XML Menu Select Tables
Import Tables Into a New Database

05. Review (7:29)

 

Access Expert 24

 

00. Intro (9:47)

01. Import RSS Feed Data (14:21)
Pull News from Web Site
RSS Feeds are XML Files
Really Simple Syndication
Get the 599CD News Feed
External Data > Import XML
ImportErrors Table
Channel and Item Tables
GUID: Globally Unique Identifier
Field truncation
Can't Save Import Steps if Error
Import Latest News from MSNBC.com
Import Tech News from CNN.com

02. Import Real-Time Weather (14:02)
How to find an RSS Feed not advertised
Weather.com RSS Feeds
Weather Underground API
Register for an API key
Import Weather XML Feed

03. Display Real-Time Weather (20:56)
Convert RFC822 to Access DateTime Value
Query Sorted by Observation Date
Form to Display Current Conditions
Button to Run Macro for Import
RunSavedImportExport
Requery Form Data
WebBrowser Control
Display Images from a Web Site
Event Timer
Timer Interval
On Timer Event

04. Online SQL Server Database (28:39)
Share Access Data via Web Server
Use Access Front End, SQL Tables
Set up SQL Server DB on GoDaddy
Database Friendly Name
Username and Password
Export to ODBC Database
File Data Source
Machine Data Source
User Data Source
Create New Data Source > SQL Server
Set up a File DSN
Complete Export of CustomerT
Link to the CustomerT on SQL Server

05. Review (5:47)

 

Access Expert 25

 

00. Intro (5:44)

01. String Functions 1 (26:27)
Function Categories
Left()
Right()
Mid()
Separate Phone Number Parts
Area Code, Prefix, Suffix
InStr()
InStrRev()
Len()
Separate First and Last Names
Get Last Name even if Middle Name
UCase()
LCase()
StrConv()
Convert to Upper Case
Convert to Lower Case
Convert to Proper Case
Trim()
LTrim()
RTrim()
Remove Extra Spaces from String
Replace()
Replace One String with Another
String Concatenation &

02. String Functions 2 (14:51)
Str()
CStr()
Convert Number to String
StrComp()
String Comparison
Greater, Less, Equal, NULL
ASC()
CHR()
ASCII Code Table
Non-Standard Characters
Space()
Monospace Fonts
Kerning, Kerned Fonts
String()
Repeat a String X Times

03. Logical Functions 1 (14:23)
IsDate()
IsNull()
IsNumeric()
IsError()
NZ()
Replace NULL Value
VarType()
TypeName()
Determining Data Types
Data Type Names
AND, OR, NOT, XOR
Logical Operators

04. Logical Functions 2(26:33)
IIF()
Nested IIF()
Student Grades Pass/Fail
Student Letter Grades
Switch()
Choose()
Shipping Methods
Calculating Employee Overtime Pay
Separating First, Middle, Last Names

05. Review (5:44)

 

Access Expert 26

 

00. Intro (06:20)

01. Math Functions 1 (27:09)
Abs - Absolute Value
Sgn - Sign
Postive, Negative, Zero
Round
Banker's Rounding
Round to Evens
Don't Want Banker's Rounding
Round to Nearest Multiple
Int - Round to Integer
Fix - Negative Int Rnd Up
Cint - Convert to Integer
Val - String to Number
Val to Convert HEX to DEC
Remove Thousands Separator
Power of Tens: 2E14

02. Math Functions 2 (12:16)
Sqr - Square Root
e - Euler's Number
Mathematical Constant
Exp - Exponential
^ Number to Power
Log - Natural Logarithm
Calculate Log of Any Base

03. Math Functions 3 (11:40)
Calculating PI
Inverse Functions Missing
Degrees to Radians
Radians to Degrees
Sin, Cos, Tan, Atn
Calculate Building Heights

04. Math Functions 4 (29:21)
Mod - Modulus
\ - Integer Division
Is Number Divisible By X
Rnd - Random Numbers
Dice Roller Form
Reroll Button
Requery Macro Command
Display Random Records
Open a Random Customer
Semi-Random Customer Codes

05. Type Conversion (9:42)
CBool - Boolean
CByte - Byte
CCur - Currency
CDate - Date
CDbl - Double
CDec - Decimal
CInt - Integer
CLng - Long Integer
CSng - Single
CStr - String
CVar - Variant

06. Review (5:11)

 

Access Expert 27

 

00. Intro (5:46)

01. Date Time Now 1 (24:49)
Inserting DateTime Shortcut Keys
Insert Date with Ctrl-;
Insert Time with Ctrl-:
Insert Date from Previous Record with Ctrl-'
Date Time Functions
Date(), Time(), Now() Functions
Display Current Date in Unbound Form Field
Use as Default Value in a Table Field
Use as Default Value in a Form Field
Format Function Parameters
Show Date as "Monday, December 15, 2014"
Two Digit Year Cutoff - 2030 Problem
Automatically Timestamp New Records
Show Records From Today
Show Records in the Past
Show Records in the Future
Show Records on a Specific Date
Show Records NOT on a Specific Date
Show Records Between Two Dates
Show Records NOT Between Two Dates
Show Records Outside of a Date Range
Show Records on Specific Dates with IN() Function

02. Date Time Now 2 (37:17)
DateTime Mathematics
Date Stored as a Number
Number of Days Since 12/30/1899
Good Enough for Most Circumstances
Watch for Dates with Times with Between
Show Records From Today With Times
Show Records Between Two Dates With Times
Show Records on a Specific Date With Times
Show Records For Tomorrow
Show Records from Yesterday
Show Records Within one week from today
Birthdays From Now until Next Week
Orders Less than One Week Old
Aged Accounts Receivable
Query with Orders, OrderDetailQ, CustomerT
Need DueDate, CompanyName, OrderTotal
Order must be an Invoice and Not Paid
Aggregate Totals Group By ID, SUM Total
Put Aging in Second Query (for clarity)
Use IIF Function to Add Aging Data
Current Orders
Less than 30 days old
Between 30 and 60 days old
More than 60 days ago
Number of Days Late
Accounts Receivable Report
Conditional Formatting to Hide Zero Amounts
Totals in Report Footer

03. Date Time Now 3 (25:24)
Hours as Fractions of a Day
One Hour in the Future
Five Minutes in the Past
CDate() Function Convert Numbers or Text as Date
Calculating Age "Good Enough" Div by 365.242
Calculating Age in Whole Years
Display Total Time Worked in hh:nn
Display as 8:30, 8.5, and "08h 30m"
Timesheet Spanning Midnight
Original Worklog used Date and Time
What if you have only Time in your log?
Showing 24:00 in WorkLog instead of 00:00
Format two digits

04. Review (6:09)

 

Access Expert 28

 

00. Intro (8:30)

01. Break Apart Dates (21:59)
Day(), Month(), Year()
Hour(), Minute(), Second()
Is date in current year
Format property display -1/0 as True/False or Yes/No
Is date in previous year
Is date in next year
Is date in current month
Is date in current year to date
WeekDay() Function
WeekDayName() Function
MonthName() Function
What Day of the Week is Today?
First Day of Week
Last Day of Week
Tuesday Following Date
Weeks that Start on a Different Day (like Monday)
What's today's Weekday if week starts on Monday?
What's the date of the Monday before today?
Is Date a Work Day (Mon-Fri)
Is Date a Weekend Day (Sat, Sun)

02. DateAdd Function (9:32)
Query Insert Columns
DateAdd() Function
One day from Date
DateAdd Format Codes
One week from Date
One month from Date
One year from Date
How DateAdd handles leap years
One week before Date
Exactly 9 months from Date
Exactly 21 years before Date
1.5 years from Date
Within one calendar month from Date
Less than one calendar month before Date
Ten Minutes from Date

03. DateDiff Function (15:32)
Calculating Difference Between Two Dates
DateDiff() Function
Number of days between two dates
Number of days since order placed
Number of months until mortgage is paid
Someone's age (not 100% reliable)
Number of weeks since Jan 1st of current year
Number of minutes worked
First Day of Week Optional Parameter
First Week of Year Optional Parameter

04. DatePart Function (16:52)
Working with individual date components
DatePart() Function
Show orders from this year
Similar things with DatePart as with the Day, Month, Year functions
Date in a specific month
Date in a specific quarter
Date in current week of year
Invalid formulas on Microsoft's web site
Date in previous week
Date in next week
Be careful of suspicious behavior around the end of the year
Use optional parameters for DatePart
Date in previous month
Date in next month
Date in current quarter
Date in previous quarter
Date in next quarter

05. DateSerial Function (17:03)
First Day of Month
Last Day of Month
First Day of Previous Month
Last Day of Previous Month
First Day of Following Month
Last Day of Following Month
First Day of Quarter
Last Day of Quarter
First Day of Year
Last Day of Year
How Many Days in Month
How Many Days in Quarter
How Many Days in Year
What Day of the Year is it?
How Many Days Remaining in Year?
Exact Age / Anniversary Calculations
TimeSerial() Function Just like DateSerial
DateValue() Function
TimeValue() Function
CDate() Function - BOTH date AND time
Useful to convert strings to DateTime values

06. Ordinal Dates (08:40)
Displaying 1st, 2nd, 3rd, 4th, etc.
Discuss VBA Function for Ordinals
Learn how to calculate with IIF Functions

07. Miscellaneous (33:02)
Calculate Someone's Next Birthday Date
List of Birthdays in Next 60 Days
How many days until your next birthday
List of Birthdays from Next Month
Use the NZ function to substitute a date
Not all customers have CustomerSince
If NULL, assume start date of business
Field Level Validation Rules (Field-Level)
Table Level Validation Rules (Table-Level)
Validation Rule ShipDate > OrderDate
Validation Rule OrderDate <= Now()

08. Review (6:36)

 

Access Expert 29

 

00. Intro (6:23)

01. Aggregate Functions (12:56)
Sum, Avg, Count, Max, Min, First, Last
Will cover StDev Later
Will cover Variance Later
Use in a Form Footer
Use in an Aggregate Query
Use Aggregate as Query Criteria

02. Domain Aggregate Functions (17:53)
DLookup, DSum, DCount, DAvg
DMax, DMin, DFirst, DLast
Domains & Criteria
Number, String, Date Criteria
No Criteria
Double Double Quotes for Strings
Largest Autonumber in Table
How Many Records in Table
Criteria on the Current Form
Criteria on Another Form
Concatenation of Criteria
Multiple Criteria
Parentheses in Multiple Criteria

03. Domain Aggregate Examples (18:14)
DLookup Sales Rep Phone Number
DSum Orders From Last 30 Days
System Default Values Table
DCount Orders in Last 30 Days
DMax Show Last Order Placed
DAvg to Show Average Sales
Calculate Average Daily Sales
Forecast Future Sales

04. Create Your Own Counter (26:39)
Creating Editable Counters
Can't put DMAX or MAX in Table Default
Expression Builder List of Functions
Try Using Form Default Value Field
Order Of Evaluation
Before Insert Event
Can't use Max in Macro
Use DMax to Find Largest Current Value
Increment Counter Value
Test Referential Integrity
Cascade Updates for ProductCategoryID
Use NZ for initial seeding
Show DMax VBA Code

05. Running Balances (24:48)
Set up Bank Account Table
Check Register
Deposits are Positive Amounts
Checks are Negative Amounts
Running Sum Property in Report
Running Sum over Group: Month
No Running Sum in Forms
Use DSUM for Running Balance
DSUM Based on the ID
DSUM Based on the Transaction Date

06. Excel VLOOKUP Replacement (14:19)
Student Letter Grade Example
Assign "A" for Grade 90, etc.
DMAX and DLOOKUP Combination
Look up Customer Credit Bracket
Gold, Silver, Bronze, etc.

07. Review (6:36)

 

Access Expert 30

 

00. Intro (6:00)

01. Functions & Interest (9:08)
Access Financial Funtions
Functions Covered Today:
PV, FV
RATE, NPER
PMT, PPMT, IPMT
Functions NOT Covered:
NPV, DDB, IRR, MIRR
SLN, SYD
Simple v. Compound Interest Rates
APR Annual Percentage Rate

02. Loan Calculator (18:08)
Calculate Mortgage Payments
PMT Function
Amount of Loan
Number of Periods
Interest Rate
Monthly Payment
Total Amount Paid
Total Interest Paid
Rounding Issues
Extra Payment at End of Loan

03. Investment Calculator (7:37)
Calculate Future Value of Investment
Amount of Investment
APR Annual Percentage Rate
Number of Years to Invest
Extra Payments
Future Value
FV Function

04. Interest Rate Calculator (11:30)
What was my rate of return ROI
Intial Investment Amount
Amount of Return
Number of Years to Invest
What was my Interest Rate
RATE Function
Why sometimes divide by 12
Annual vs. Monthly Compounding

05. Millionaire Calculator (9:30)
How long until I'm a millionaire
Initial Deposit Amount
Monthly Payment Amount
Goal Amount
Interest Rate
Calculate Number of Months
Convert to Whole Years
INT Function
NPER FUnction

06. Initial Deposit Calculator (7:06)
College fund calculator
How much deposit to hit goal return
Current Balance Amount
Interest Rate
Years to have account open
Extra annual payments
PV Function

07. House Value Calculator (5:55)
Monthly payment you can afford
Interest rate from bank
How many years for your mortgage
Calculate house you can afford
PV Function

08. Loan Amortization (22:47)
How Amortization Works
Interest and Principal Portions
Build LoanF Form
Create AmortT Table for Periods
Build AmortF Subform
Link Child and Master Fields
Calculate Interest Paid
IPMT Function
Calcaulte Principal Paid
PPMT Function
Rounding Issues
IIF Function to Hide Errors

09. Review (6:51)

 

Access Expert 31

 

00. Intro (4:55)

01. Charts 1 (11:22)
What is a Chart
Why are Charts Useful
Chart Area
Data Series
Data Points
Chart Axes
Horizontal X Axis
Vertical Y Axis
Chart Title & Legend
Chart Gridlines
Create a Sales Chart
Chart Wizard
Add Chart to Form
Chart Types

02. Charts 2 (10:52)
Chart Formatting Options
Chart Datasheet
Components Dropdown
Edit Chart Title
Format Data Series
Change Colors
Fixing Distortion
Clip, Stretch, Zoom

03. Charts 3 (10:40)
Transparent Chart Background
Multiple Data Series
Preview Chart Button
3-D Column Chart
3-D View & Effects
Elevation, Rotation

04. Charts 4 (12:03)
Data Series Shape
Data Labels, Value
Gap Width, Depth
Chart Depth
Format Walls, Floor
Aggregating Data
Add Trendline
Pie Charts
Angle of First Slice
Data Labels
Leader Lines

05. Charts 5 (9:28)
Review PCResale Database
Sales by Rep previous 30 days
Chart Dashboard
Setup Queries

06. Charts 6 (11:13)
Build Dashboard
Chart in a Report

07. Review (4:49)

 

Access Expert 32

 

00. Intro (4:10)

01. Single Letter Report Header (18:05)
Contact Report
Creating Group for Single Letter
Group by First Character
Display LEFT 1 Character
Group by Contact Name
IsNull to Show No Company Name

02. Report List Form (17:19)
List of All Reports in Database
MSysObjects Table
List of Type Values
Custom Report List Table
Report List Form and Listbox
Macro to Open Report
Build Event
On Click Event

03. Rank, Median, Mode (25:04)
SubQueries, SubQuery
Query Customers Less Than Average Employees
Rank Formula
Alias for a Table
Median Value "Good Enough"
Mode Value
Totals Query, Top 1 Value
Percentile Ranking

04. Intro to Macros (36:18)
Customer Mailing Labels
Create LabelT Table
Create LabelR Report
Insert Blank Records at Top
Cut and Paste
Create LabelF Form
Create View Labels Button
Print Preview Report
Macro to Insert Blank Records
Command Button Macro
GoToRecord New Record
SetValue
Show All Actions
Unsafe Actions
Form Macros v External Macros
Button to Run External Macro
Prompt For Loop Count Value
InputBox Function
Insert Blanks on Top of Label Sheet
RunMenuCommand
SelectRecord, SelectAllRecords
Cut, Paste
Button on Customer Form to Add Label
OpenForm

05. Review (5:12)

 

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

10/21/2021Conditional Formatting
10/20/2021Text Messages
10/19/2021Late Binding
10/18/2021Open Form
10/17/2021Date Only?
10/16/2021Relink Tables
10/16/2021Access Developer 34
10/16/2021Access Developer 34 Lessons
10/16/2021Access Developer 34
10/10/2021Copy Text from Field
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Page Tag: outline access 00 02 nocolumns  PermaLink