Access 2007-2019 Access 2000-2003 Access Seminars TechHelp Support Tips & Tricks Access Forum Course Index Topic Glossary Insider Circle

 Home   News   Courses   Seminars   Templates   Help   TechHelp   Forums   Order   Contact   Logon More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

 < 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:

 - 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,

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.

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.

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.

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.

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.

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.

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, LastWill cover StDev LaterWill cover Variance LaterUse in a Form FooterUse in an Aggregate Query Use Aggregate as Query Criteria 02. Domain Aggregate Functions (17:53) DLookup, DSum, DCount, DAvgDMax, DMin, DFirst, DLast Domains & CriteriaNumber, String, Date CriteriaNo CriteriaDouble Double Quotes for StringsLargest Autonumber in TableHow Many Records in TableCriteria on the Current FormCriteria on Another FormConcatenation of CriteriaMultiple CriteriaParentheses in Multiple Criteria 03. Domain Aggregate Examples (18:14) DLookup Sales Rep Phone NumberDSum Orders From Last 30 Days System Default Values TableDCount Orders in Last 30 Days DMax Show Last Order PlacedDAvg to Show Average Sales Calculate Average Daily SalesForecast Future Sales 04. Create Your Own Counter (26:39) Creating Editable CountersCan't put DMAX or MAX in Table DefaultExpression Builder List of FunctionsTry Using Form Default Value FieldOrder Of EvaluationBefore Insert EventCan't use Max in MacroUse DMax to Find Largest Current ValueIncrement Counter ValueTest Referential IntegrityCascade Updates for ProductCategoryIDUse NZ for initial seedingShow DMax VBA Code 05. Running Balances (24:48) Set up Bank Account TableCheck RegisterDeposits are Positive AmountsChecks are Negative AmountsRunning Sum Property in ReportRunning Sum over Group: MonthNo Running Sum in FormsUse DSUM for Running Balance DSUM Based on the IDDSUM Based on the Transaction Date 06. Excel VLOOKUP Replacement (14:19) Student Letter Grade ExampleAssign "A" for Grade 90, etc. DMAX and DLOOKUP CombinationLook 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

 You may want to read these articles from the 599CD News:
 10/21/2020 Consolidate Tables with Helper Data 10/20/2020 Is Microsoft Access Going Away? 10/12/2020 Store Multiple Phone Numbers 10/10/2020 ABCD Part 1: Core Database 10/10/2020 Create Your Own Custom Function 10/8/2020 Update Query from Another Table 10/5/2020 Access Business & Contact Database 10/5/2020 How and Why to Split Your Database 10/2/2020 Multiple Nested Continuous Subforms 9/28/2020 Access Developer 18

 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 Free Upgrades Insider Circle Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Articles Search The Site Waiting List Production Schedule Collapse Menus Help  Live Chat Customer Support WalkThru Tutorials Troubleshooting FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks MYOLP Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Live Chat General Info Support Policy Contact Form Email Richard Mailing Address Phone Number Fax Number Course Survey