|
|
|
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,
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: |
This class was recorded with Microsoft
Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years. |
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: |
$30.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, 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 |
|
|
|