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  

News      User Comments     History     Notify Me

6/6/2015 12:08:35 PM
Microsoft Access Expert 29
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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.

Permanent Link
Course Link: Microsoft Access Expert 29
Keywords: access expert 29 domain aggregate counter running sum running balance vlookup
Page Tag: whatsnew
Post Reply

Next Counter Id Comment from Dennis Owens @ 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.
Show Just This Thread        Post Reply
Microsoft Access beginner level 2 Comment from kelvin lubinda @ 3/9/2016
Very good lessons, consider doing a lesson on Pastel full tutorial i can buy
Show Just This Thread        Post Reply
Microsoft Access 2013 Comment from Herbert @ 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
Show Just This Thread        Post Reply
Union Query Comment from Kenny Nelson @ 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!
Show Just This Thread        Post Reply
Union Query Comment from Kenny Nelson @ 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?
Show Just This Thread        Post Reply
Microsoft Access Expert 29 Comment from LB6507 @ 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.
Show Just This Thread        Post Reply
Bank Seminar Comment from satish narayan @ 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.
Show Just This Thread        Post Reply
Microsoft Access Expert 29 Comment from SYLVESTER @ 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.
Show Just This Thread        Post Reply
Running Balance Comment from Joseph Mazzeo @ 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.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 6+7:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



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

6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled

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 Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
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 Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP