Introduction

Welcome! Aggregate & Domain Lookup Queries

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 29. In this course we will continue our comprehensive function guide by focusing on aggregate functions such as sum, average, count, max, and min, as well as domain aggregate functions like d-lookup, d-sum, and d-count. We will discuss how to build your own counter variable, create running sums in reports and forms, and use Access for tasks similar to Excel's v-lookup. This course is intended for students who have completed the previous Expert levels, and you will find references to related seminars and additional learning resources.

Navigation

Prev: X29
Next: Lesson 01

Keywords

Access Expert, aggregate functions, sum, average, count, max, min, domain aggregate functions, dlookup, dsum, dcount, dmax, davg, lookup functions, running sum, running balance, custom counter variable, vlookup replacement, sales forecasting

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 29, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's class is Part 5 of my Comprehensive Function Guide to Microsoft Access.

Part 1, which was Access Expert 25, covered string and logical functions. Part 2 covered math and type conversion functions. Parts 3 and 4 covered date and time functions. There are a lot of those.

Part 5, which is this class, covers aggregate functions, such as sum, average, count, max, min, where you can aggregate or sum up, for example, a bunch of records. It also covers the domain aggregate functions, where you can use the same kinds of functions across any table or query in the database. These are also called the lookup functions. You can use the lookup to look up a value in a different table or query.

Before taking this class, I strongly recommend you have taken Access Expert Level 28 and everything before it. Some of these functions, like the lookup and the sum, I have covered before. I am going to cover them again in today's class, but I am going to go over them a little faster if I have covered them already in previous classes. You will get all the information you need to know today, but it will be helpful if you have taken the previous lessons too. All my classes are designed to be taken in order, one after the other.

This class was recorded using Access 2013. All of the functions covered today should work just fine in 2010 and 2007. I am pretty sure everything covered today also works in 2003 and earlier, but I cannot guarantee it. If you are using these earlier versions of Access and you find something that does not work, please let me know.

My courses are broken up into Beginner, Expert, Advanced, and Developer Level Classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the Expert level classes, which you are in now.

When you finish all the Expert level classes, the Advanced classes will cover event programming and macros, and the Developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning, or tell me what is wrong in the student forum, and I will do my best to help you. Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases.

Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson 1, we are going to cover the different aggregate functions: sum, average, count, max, min, first, and last.

In lesson 2, we are going to cover the domain aggregate functions: d-lookup, d-sum, d-count, and so on.

In lesson 3, we are going to go over some examples of some popular domain aggregate functions. We are going to see examples for d-lookup, d-sum, d-count, d-max, d-average, and I will show you how to do some real basic sales forecasting.

In lesson 4, I am going to teach you how to create your own counter variable. This is similar to an auto-number, in that when you type in a new record, it will automatically add one to the previous ID. However, with this technique, you will have the ability to edit that ID if you want to later. You can change it from 2 to 200, and the next one that you add will be 201, and so on. So you can create your own counter variable.

In lesson 5, I am going to teach you how to create a running sum, also known as a running balance, in both a report and a form.

In lesson 6, I am going to teach you how to use Access to replace the v-lookup statement from Excel.

Quiz

Q1. What is the main focus of Access Expert Level 29?
A. Aggregate and domain aggregate functions in Access
B. Advanced VBA programming
C. SQL Server integration
D. Designing advanced forms only

Q2. Which is NOT an aggregate function discussed in lesson 1?
A. Sum
B. Average
C. Filter
D. Max

Q3. What are domain aggregate functions also known as?
A. Lookup functions
B. Formatting functions
C. Action queries
D. Data entry shortcuts

Q4. Before taking this course, what is strongly recommended?
A. Completing Access Expert Level 28 and prior levels
B. Setting up SQL Server Express
C. Downloading Access 2003
D. Learning advanced macros

Q5. If you find any topic confusing, what is suggested?
A. Watch the video again or ask in the student forum
B. Skip to the next class
C. Contact Microsoft support
D. Change to Excel

Q6. Where can you find the sample databases from the courses?
A. accesslearningzone.com/databases
B. Microsoft.com/help
C. In the Access installation folder
D. Through the Windows Store

Q7. What is the approach recommended for learning each lesson?
A. Watch the lesson once, then follow along and recreate the database
B. Apply the lesson to your most complex project first
C. Skip to the last lesson and work backwards
D. Only read the provided documentation

Q8. Which lesson covers creating your own counter variable?
A. Lesson 4
B. Lesson 1
C. Lesson 2
D. Lesson 6

Q9. In which versions of Access should the functions covered in this class work?
A. 2010, 2013, and 2007 (most likely earlier versions as well)
B. Only Access 2013 and 2021
C. Access for Mac only
D. Only Access 2019

Q10. Which topic is specifically mentioned as a replacement for an Excel function?
A. V-lookup in Access (Lesson 6)
B. IF statements in Excel
C. SUMIF in Excel
D. Pivot tables

Q11. Which domain aggregate functions does the class provide examples for?
A. D-lookup, D-sum, D-count, D-max, D-average
B. D-format, D-insert, D-append, D-delete
C. D-macro, D-vba, D-import, D-export
D. D-print, D-email, D-export, D-query

Q12. What is the purpose of the student forums mentioned in the lesson?
A. To ask questions and join discussions about the lessons
B. To submit your completed projects for grading
C. To contact Microsoft support staff
D. To find promotional coupons

Q13. What is a running sum or running balance in Access, and where is it implemented according to the class?
A. A value that accumulates over records, shown in reports and forms (Lesson 5)
B. A type of macro that deletes records
C. A fixed value that never changes
D. A print-only total

Answers: 1-A; 2-C; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Microsoft Access Expert Level 29, Part 5 in my Comprehensive Function Guide series. I am Richard Rost, your instructor for this course.

In the earlier lessons, we started our journey with functions in Access. Access Expert 25 introduced string and logical functions. We then moved on to math and type conversion functions in Part 2. Parts 3 and 4 covered a broad range of date and time functions, which are numerous and foundational for many database operations.

Now, in Part 5, we are turning our attention to aggregate functions. These include sum, average, count, max, min, and similar functions that allow you to perform calculations over multiple records in your tables or queries. We'll also focus on the domain aggregate functions, often referred to as the D-functions like DLookup, DSum, and DCount. These are powerful tools for looking up or calculating values across any table or query in your database, not just the current dataset. Some people call these lookup functions, and they are central to a lot of Access development.

If you have not yet completed Access Expert Level 28 or the previous levels, I highly recommend doing so before starting this one. Some of these aggregate and lookup functions were covered before, and while I will recap them today, I will refer back to previous explanations a bit and move a bit faster through familiar ground. Every class in this series is intended to build sequentially, so working through them in order is the best way to follow along and avoid missing important concepts.

This course was recorded using Access 2013, but all of the functions discussed should work well in Access 2010 and 2007. Most of these features even go back as far as Access 2003 or earlier, but I cannot guarantee full compatibility that far back. If you encounter something that does not work in an earlier version of Access, let me know.

The Access Learning Zone curriculum is organized by levels: Beginner, Expert, Advanced, and Developer. Beginner classes are for those who are new to Access and cover all the basics. As an Expert level student, you should already be comfortable with the foundational topics. Advanced classes will introduce you to event programming and using macros, while Developer classes will bring you into Visual Basic for Applications (VBA). Each group of classes is further divided into Level 1, Level 2, and so on.

Beyond the standard courses, I also offer specialized seminars that cover focused topics in more detail. These range from building web-based databases and working with calendar-style forms and reports, to database security, incorporating images and attachments, managing accounts payable, learning SQL, setting up loan amortization, and much more. You can find full details about all these seminars on my website.

For questions related to today's lessons, I encourage you to make use of the student forums on my website. If you are watching this in the online theater, the discussion window appears right next to each lesson video. There, you can read questions from other students, review my responses, and add your own comments. Even if you are watching elsewhere, you can always visit the forums directly on the site.

To get the most benefit from this course, I suggest watching each lesson all the way through once before doing anything in Access. Then, on your second pass, follow along with my examples by actually building the same database I do in the video, step by step. Do not attempt to apply these concepts to your other projects until you have worked through the class examples and are comfortable with them.

If you run into trouble or find something unclear, rewatch the video from the beginning, or reach out in the student forums and I will help you work through your issue. Make sure to keep an open mind. Access can seem overwhelming at first, but as you progress, you will see it is quite approachable.

While I strongly urge you to build your own copy of the database alongside the lessons, if you would prefer, you can also download my finished database file from my website. Sometimes the best way to learn is by taking apart someone else's work. This was how I learned Access years ago - by exploring Microsoft's Northwind Traders sample database. You'll find a sample database available for every lesson on my site.

Let me outline what we'll be covering in today's class:

Lesson 1 focuses on the main aggregate functions: sum, average, count, max, min, first, and last.

Lesson 2 goes over the domain aggregate functions, including DLookup, DSum, DCount, and more.

Lesson 3 presents practical examples of these domain aggregate functions, demonstrating how to use DLookup, DSum, DCount, DMax, DAvg, and applying these techniques to do some basic sales forecasting.

In Lesson 4, I will show you how to create your own counter variable. Similar to Access's AutoNumber field, this allows you to automatically generate sequential IDs when you add new records. The key difference is that with this method, you can edit IDs freely if needed, and the next value will increment from whatever you set last.

Lesson 5 will teach you how to build a running sum, also known as a running balance, which you will learn to implement both in reports and on forms.

Finally, Lesson 6 demonstrates how you can use Access to replicate the behavior of Excel's VLOOKUP function.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Aggregate functions: Sum, Avg, Count, Max, Min, First, Last
Domain aggregate functions: DLookup, DSum, DCount
Examples using domain aggregate functions
Real basic sales forecasting with domain aggregates
Creating your own counter variable
Editable auto-number style counters
Creating a running sum in a report
Creating a running balance in a form
Replacing Excel VLOOKUP with Access functions

Article

Welcome to this tutorial on aggregate and domain aggregate functions in Microsoft Access. In this article, I will guide you through the concepts of summing and analyzing records in your database using functions like sum, average, count, max, min, and more advanced domain aggregate functions such as DLookup, DSum, and DCount. These tools are essential for anyone who wants to summarize data or pull values from different tables or queries.

Before moving forward, it is important to have a solid understanding of the basics in Access, like creating tables, queries, and forms. If you feel comfortable with those topics, you will get the most out of these advanced techniques.

Let us start with aggregate functions, which are used to perform calculations on a group of records. For example, if you have a table of sales records and you want to know the total sales for the month, you would use the Sum function. Here is a simple example in a query:

SELECT Sum([SaleAmount]) AS TotalSales FROM Sales;

This SQL statement adds up all the values in the SaleAmount field and displays the total as TotalSales.

Other popular aggregate functions include Avg, which calculates the average value, Count, which counts the number of records, Max, which finds the maximum value, Min for the minimum value, First, and Last. Here is how you might use these in a query:

SELECT
Sum([SaleAmount]) AS TotalSales,
Avg([SaleAmount]) AS AverageSale,
Count([SaleAmount]) AS NumberOfSales,
Max([SaleAmount]) AS HighestSale,
Min([SaleAmount]) AS LowestSale
FROM Sales;

This example will give you a summary of your sales in one view. These functions are most often used in queries by switching the query into Totals view (click the Totals button in Query Design) and selecting the appropriate function from the drop-down list under each field.

Sometimes you need to look up or aggregate data from a different table or based on specific criteria. This is where domain aggregate functions come into play. The most widely used function is DLookup, which retrieves a single value from a table or query. For example, suppose you want to display a customer's email address on an order form. You could use:

=DLookup("Email", "Customers", "CustomerID = " & [CustomerID])

This looks into the Customers table, finds the record where the CustomerID matches the one on your current form, and returns the Email field.

In a similar way, DSum calculates the total of a numeric field over a subset of records. For example, to get the total sales for a particular customer you could write:

=DSum("SaleAmount", "Sales", "CustomerID = " & [CustomerID])

This sums the SaleAmount values from the Sales table where the CustomerID matches the value on your current record.

You can also use DCount to count the number of related records. If you want to count the number of invoices a customer has, you might use:

=DCount("InvoiceID", "Invoices", "CustomerID = " & [CustomerID])

Domain functions also let you find the maximum, minimum, and average using DMax, DMin, and DAvg. For example:

=DMax("OrderTotal", "Orders", "CustomerID = " & [CustomerID])

This gives you the largest single order total for a given customer. With DAvg, you get the average.

A practical example might be a quick sales forecast. Suppose you have a table of sales and want to forecast future sales based on an average of the previous few months. You could use DAvg combined with criteria that restricts data to a certain date range, like sales in the last 90 days.

Now let us discuss how to create a custom counter variable, which behaves like an auto-number field but is editable. Say you need an invoice number that can be manually changed. First, create a number field in your table, call it InvoiceNumber. Next, use DMax to find the current maximum invoice number. In the Default Value property of the field on your form, enter:

=Nz(DMax("InvoiceNumber", "Invoices"), 0) + 1

The Nz function ensures you get 0 if there are no records yet, so the first invoice will be 1. If you change an invoice number to something higher, say 200, the next one automatically becomes 201.

For some applications, you may need a running total, sometimes called a running sum or balance. For a basic running sum in a report, open your report in design view, add a text box to the detail section, and set its Control Source to the sum you want to keep track of, like:

=Sum([Amount])

If you want a running total up to the current record, select the text box, go to its properties, and set the Running Sum property to Over Group or Over All depending on your needs.

To do this on a form, you would typically use VBA. For example, suppose you have a continuous form showing account transactions, and you want to display the current balance after each transaction. In the form's recordset or in the AfterUpdate event, you could write VBA code like this:

Dim rs As DAO.Recordset
Dim runningTotal As Currency
Set rs = Me.RecordsetClone
runningTotal = 0
rs.MoveFirst
Do While Not rs.EOF
runningTotal = runningTotal + rs!Amount
rs.Edit
rs!RunningBalance = runningTotal
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

This iterates through all the records and updates the RunningBalance field with the running total.

Lastly, many users are familiar with Excel's VLOOKUP and want to do something similar in Access. DLookup is the best tool for this job. If, for instance, you want to auto-fill a product price based on a selected product ID in your order details form, you could use:

=DLookup("ProductPrice", "Products", "ProductID = " & [ProductID])

This fetches the price from the Products table where ProductID matches what is selected in your order detail.

Using DLookup and the other domain aggregate functions lets you grab values from other records, tables, or queries, making your Access database much more interactive and powerful.

In summary, aggregate and domain aggregate functions in Access provide a robust way to summarize and analyze data, build running totals, create editable counters, and link information together just like Excel's lookup functions. Experiment with the examples here and try applying them to your own database. If you get stuck, review the concepts, make sure your field and table names match, and remember that practice is key to mastering these powerful tools.