Hey guys. Sorry I've been gone so long. Had to go out of town for a bit, then I got sick and that took a few weeks to clear up... but I'm back in the saddle now.
I just finished Access Expert Level 29. The videos are online in the Theater, and you can order it now if you want. This class covers aggregate (Sum, Avg, etc.) and domain lookup functions (Dlookup, Dmax, Dcount, etc.) in detail. I'll post the full outline with screen shots later.
If you're a Learning Connection member or are on the Waiting List for Access, I'll try to process the auto orders later tonight or tomorrow. Thank you.
Now that I'm better, I plan to get a lot more recording done over the next couple of weeks. I started recording a Windows 8.1 class (redo Level 1, new Level 2) and I want to finish it since I've invested so much time into it already - even though Windows 10 will be coming out next month. So look for that soon.
Lots of people have asked about several different Access seminars, so I'll be picking one of those very soon as well.
The next Access class (X30) will cover financial functions, a mortgage calculator, and lots more. Cool stuff coming up.
-----
Outline for 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)
11 years ago
