Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon  
NEW: Excel Expert 11 - Learn XLOOKUP    dismiss
I'm Back! New Access Course Finished
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   6/2/2015 3:28:25 PM

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)

I m Back New Access Course Finished Link 
Cheryl H - 8/13/2015
My developer son tells me that Microsoft plans to phase out Access and discontinue supporting it.  What do you know about that possibility?  Being sick sure puts a kink in future plans.  Glad to hear your better. Read More...
Add Your Comments
Advanced Access 2010 Link 
Jeff S - 6/29/2015
Hi Richard,

I enjoyed your series very much and you are by far the best instructor I have found across the web!

Do you have any ETA on Access Advanced/Developer for 2010?

I'm sure a lot has changed since 2003 and I don't want to have to relearn or re-watch to learn all the new tips and tricks.. I'd rather wait! Read More...
Add Your Comments
I m Back New Access Course Finished Link 
Robert S - 6/9/2015
Good to have you back Richard, I am itching to proceed further

Add Your Comments
I m Back New Access Course Finished Link 
Deon R - 6/4/2015
Welcome back Richard :)and I'm so looking forward to more courses and seminars - 'all things Access' :)

Alex's done a Stirling job during this time. Long may he continue to post and assist.
Add Your Comments
I m Back New Access Course Finished Link 
Robert Balagot - 6/4/2015
Welcome back Rick!  Hope you have more tutorial that you can share.  I went through all the Access Level and it's very impressive.  It was the best ever.  Thanks.
Add Your Comments

Show Older Posts


Post Your Comments
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

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

5/30/2020Two New Access Handbooks
5/30/2020Access Tip Sleep Timer
5/18/2020Access Record Source Property
5/3/2020Access Replace Function
5/1/2020Microsoft Access Tutorials
4/24/2020Access Discontinued?
4/21/2020Date Time Clock Picker Template
4/20/2020Access Backup Record Before Edited
4/17/2020ZIP Code Lookup Template
4/16/2020Access Round Nearest 10

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed