Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 

< Previous: Access Expert 28

Next: Access Expert 30 >

Access Expert Level 29

Expert Microsoft Access Tutorial - 2 Hours, 7 Minutes
 
 
Access Expert 29 is Part 5 of our Comprehensive Guide to Access Functions. Today's class focuses on Aggregate and Domain Aggregate (Lookup) Functions. You will learn about lots of different functions, such as Sum, Avg, Count, Max, Min, DLookup, DCount, DMax, DSum, and many more. We've briefly covered some of these functions in previous classes, but only a little bit. Today's class dives deep into this very important topic. You will learn:
 
access cd   - 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

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 29
Description: Access Expert Level 29
Versions: Recorded with Access 2013. Most of the material should work with all versions of Access. The majority of the functions covered in today's class go all the way back to the first versions of Access.
Pre-Requisites: Access Expert Level 28 strongly recommended, as you should watch all of my classes in order. However, if you're skipping around, you should have at least completed the Beginner series and the first 3 or 4 levels of the Expert series where I start to cover functions.
Running Time: 2 Hours, 7 Minutes
Cost: $26.99


This is part 5 of the Comprehensive Guide to Access Functions. We will be learning many new Aggregate and Domain Aggregate (Lookup) functions in today's class. We will start with the basic aggregate functions: Sum, Avg, Count, Max, Min, First, Last. We will see how to use them in a form footer, and an aggregate query.

aggregate functions

 

Next we will learn about Domain Aggregate functions, also called Lookup Functions. These include DLookup, DMax, DMin, CSum, DCoun, DAvg, DFirst, and DLast. We will spend some time covering the different types of criteria for these functions, which is a major source of headache for many users.

domain aggregate lookup functions

 

Next we go over a bunch of different examples using the lookup functions. We will see how to display your order totals for the past 30 days, how to read values out of the system default values table, how do display the date of the last order placed, how to show the average order value, how to forecast future sales, and more.

lookup examples

 

Next we will cover a topic that tons of people have asked me about: how to create your own counter field in Access. Many people want to have a number that automatically increments, but they don't want to use an Autonumber, for a variety of different reasons. So in this lesson, I'll show you how to create your own ID field and have it automatically count up from whatever the largest current ID field is, so you can start it at 1000 and the next number would be 1001, for example.

create your own counter field

 

The next example is also highly requested. You will learn how to display running balances (or running sums) in your Access forms and reports. Creating a running sum in a report is relatively easy, but unless you know how to use the domain aggregate functions, it's impossible to do with forms - but I'll show you how.

running sum running balance

 

Finally, we'll finish up with another popular request: how to recreate Excel's VLOOKUP function in Access. In Excel, if you want to look up a value in a table that has a RANGE of values, you can use VLOOKUP. Access doesn't have an equivalent function. For example, let's say you're looking up student grades. Your lookup table might have 0, 65, 70, 80, and 90 in it, and you want to be able to look up, say, 87, but you don't want to specify every possible grade from 0 to 100. So we can use a combination of domain aggregate functions to duplicate VLOOKUP.

vlookup in access

 

This is the 29th class in the Access Expert series. This is the fifth class in my Comprehensive Function Guide series. If you're serious about building quality databases with Access, don't miss out on this course. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 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)

 


 
Keywords: Comprehensive Function Guide, Aggregate and Lookup Functions, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, Sum, Avg, Count, Max, Min, First, Last, DLookup, DSum, DCount, DAvg, DMax, DMin, DFirst, DLast
 
 

Student Interaction: Microsoft Access Expert 29

Richard on 6/6/2015:  Microsoft Access Expert 29Access Expert 29 is Part 5 of our Comprehensive Guide to Access Functions. Today's class focuses on Aggregate and Domain Aggregate (Lookup) Functions. You will learn about lots of different functions, such as Sum, Avg, Count, Max, Min, DLookup, DCount, DMax, DSum, and many more. We've briefly covered some of these functions in previous classes, but only a little bit. Today's class dives deep into this very important topic. You will learn: - 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 Click here for more information on Access Expert Level 29, including a course outline, sample videos, and more. This course was recorded using Access 2013, but most of the functions covered are valid for all versions of Access. This class follows Expert Level 28. The next class in the series is Expert Level 30.
Joseph Mazzeo on 6/10/2015: The running balance is great! I've needed to know some of these tips and tricks that you showed. Could you do a session on stored running balance where you can input last month's balance and manipulate it as you need and store the new balance?

Reply from Alex Hedley:

You could use a DOMAIN LOOKUP function covered in Expert 29 to get the last one ready to use in your Form.

SYLVESTER on 6/12/2015: If you were building a commercial application in Access and wanted to provide a fairly Straight forward way for users to perform a "Backup" of the database and then a way for users to "Restore" in case they had to, How would you provide this functionality

Reply from Alex Hedley:

I would do it with a Windows Scheduled Task to backup.
You could also add a button to the Ribbon, there is a backup option within the commands.
For restore you could have a link to your backup folder and get the latest date one.

satish narayan on 7/2/2015: I am very interested in full seminer on cash register. Can it be designed to include many Bank Accounts,many Branches using same Bank Account with many transcation types such as Cheque,Cash,Direct Debit/Credit card deposits etc.Could you split this seminar in parts so that we can purchase the parts that has already been completed

Reply from Alex Hedley:

Parts of this have already been written, keep an eye out for it being released.

Blaise on 7/6/2015: Thank y ou
RAJNIKANT K PATEL on 7/26/2015: i like your access video..

keep it up..

LB6507 on 7/27/2015: Do you have anything on creating a table where it can track students and training classes at work. I want to get ideas on how to track which class they need to take specific to their job role. I would like to track the frequency of that training and send an email to remind the managers with the names of their direct reports and the name/date of their next training.

Reply from Alex Hedley:

There is a Tip on an APPEND Query that uses the Student Tracking db.

Kenny Nelson on 11/23/2015: Does this running balance logic work in a Union Query? I haven't been able to get it to work. I've made sure my field names are the same in both segments of the query, but I continue to get an error message that just says "Unknown".

Reply from Alex Hedley:

I'm sure there is a bug in Access for the Union button. If you try just the SQL button then type in your UNION statement does it work?

Kenny Nelson on 11/24/2015: Regarding previous post, I first created the union query. I then used the results from that query for my running balance query. Worked like a champ!
Herbert on 3/9/2016: Whenever I create a splitform, I seem to be unable to narrow the width of the form. It uses the entire width of my screen. Can you advise help

Reply from Alex Hedley:

I think that's intended, you could try a Form and SubForm

kelvin lubinda on 3/9/2016: Very good lessons, consider doing a lesson on Pastel full tutorial i can buy
Dennis Owens on 5/27/2016: Will this work in a field where you are giving (say a wristband) that reads 258, 259, 260, etc and when you enter a new order and the last wristband was 260, the next order will be 261. If so, if you enter an earlier number say 158, would the next new number still be 261.

Reply from Alex Hedley:

If you are using a DMAX it will.

Trina Barnwell on 10/14/2016: Please give me instruction on how to get my training videos I purchased for level 2 and 3 of Access. Thanks

Reply from Alex Hedley:

Check out the WalkThru

When you're logged in there is a link to the Online Theater.

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP