Introduction

Welcome! Aggregate Queries: Sum, Avg, Total

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

Welcome to Microsoft Access Expert Level 11. In this course we will focus on aggregate queries, also known as summary queries, grouping queries, or totals queries. We will cover what aggregate queries are, when and how to use them, and walk through building queries to calculate totals, averages, counts, and more, such as sales by state or month, customer credit limits, and lowest product costs by vendor. We will also discuss advanced query criteria, creating employee work logs, using the D-Sum function, using the expression option, and provide guidance on getting the most from the lessons and student forums.

Navigation

Prev: X11
Next: Lesson 01

Keywords

Access Expert, aggregate queries, summary queries, grouping queries, totals queries, sum function, average function, count function, min function, max function, D-Sum, D-Lookup, sales by state, sales by month, employee work log, customer last contact, ven

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 11, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is all about aggregate queries, also known as summary queries or grouping queries or totals queries. They have lots of different names.

We will begin by learning what aggregate queries are and when you want to use them. We will build some simple aggregate queries, such as, show me all of the sales totals broken down by state. We will learn about all the different aggregate query functions, like sum, average, count, max, min, and so on.

We will learn about complex query criteria for our aggregate queries. We will perform some more advanced calculations such as, show me the credit limit of all of my customers broken down by state, but only for customers after 1999 and things like that. We will learn about the where field condition in our aggregate queries.

Then we will go through a few more examples of aggregate queries such as, show me sales totals by month, show me the lowest product cost, since each of my products can be purchased from multiple vendors and each vendor sells multiple products. Remember we built that many to many relationship. I want to generate a list of all of my products and show me the lowest cost for each product and which vendor I can buy it from.

Next is the last customer contact report. Show me a list of all of my customers, the date of the last contact with them, and what that contact was, and if there are no contacts, show that. This is great for seeing which customers you have not talked to in a while, and also for a brief synopsis of where each customer is at.

Next we will build an employee work log where we can track employee time. Pick the employee, put in their start date and end date, or start time and end time for when they check in. Then using an aggregate query, we can generate a report showing all of their hours worked between two dates. If you want to get their hours worked for the week, you just type in those dates, hit run query, and there is your report.

Finally, we will go over a bunch of little stuff. We will cover the expression option in aggregate queries. We will use the D-Sum function, which is an aggregate function, to show the orders for the last 30 days right on the main menu, and we will make a little refresh button so we can click on it and recalculate that value. Of course there are lots of little tips and tricks to throw in throughout the lesson.

This course follows Microsoft Access Expert Level 10. I recommend you watch Level 10 before watching today's class. Level 10 covers the D-Lookup function, which is very similar to the D-Sum function. We are going to work with that later today. It would be helpful if you understand D-Lookup first before learning about D-Sum. We cover generating quotes and invoices and using calculated table fields in Level 10. You can find more information on Level 10 on my website at accesslearningzone.com.

Today's class is recorded using Microsoft Access 2013. Everything in today's class should work just fine with 2007 and 2010. If you are using Access 2003, go to my website and get a copy of Access 221 from my older series. That class is roughly equivalent to this one, covering aggregate queries and such.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer.

The Beginner lessons are designed to give you a basic overview of the simple features of Access. The Expert series, the one you are in now, is designed for more experienced users who are already comfortable with Access. The Advanced lessons cover working with macros, automation, and many more advanced features. The Developer lessons get into programming with Visual Basic for Microsoft Access. Each of the series is broken down into different numbered levels, starting with Level 1. The Beginner series, for example, had Levels 1 through 9.

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, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all 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 using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos as long as you have an active internet connection.

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 may have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well. If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that 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 this 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. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now let us take a closer look at exactly what is covered in today's class.

In Lesson One, we are going to learn what aggregate queries are, why they are useful, and we will build a few simple queries so you can see how they work.

In Lesson Two, we are going to look at building an aggregate query that has complex query criteria.

In Lesson Three, we will make an aggregate query to show you sales by month. We will show you the sum, average, and count of all of your sales grouped by month.

In Lesson Four, we will use a minimum aggregate query, along with some D-Lookup statements, to generate a list of products and the lowest possible price you can get it from each vendor. Since each of our products can be purchased from multiple vendors, we might want to know who are the cheapest vendors for each product. To do that, we can use a minimum aggregate query.

In Lesson Five, we will use an aggregate query to generate a list of all of our customers, when the last contact date was for each customer, and what was discussed.

In Lesson Six, we will build an employee work log. We will create a simple time clock table with a time in and a time out each day, we will calculate the hours worked, and we will make a lookup form so you can type in a start date and an end date, pick an employee from a combo box, and then using an aggregate query, see the sum of hours worked between those two dates.

In Lesson Seven, we are going to talk about a couple of miscellaneous items. We are going to learn about the expression option in your aggregate queries. We are going to make a box on your main menu showing the orders in the last 30 days using the D-Sum function, we will make a button to refresh that value manually, and I will give you your homework assignment for the next class.

Quiz

Q1. What is another name for aggregate queries in Microsoft Access?
A. Summary queries
B. Parameter queries
C. Action queries
D. Append queries

Q2. Which of the following is NOT an aggregate function mentioned in the lesson?
A. Multiply
B. Sum
C. Average
D. Count

Q3. What can aggregate queries be used for, according to the video?
A. Grouping data and calculating totals
B. Deleting duplicate records
C. Importing data from Excel
D. Sorting records alphabetically only

Q4. Why should you watch Expert Level 10 before taking this course?
A. It covers D-Lookup, which is similar to D-Sum used in Level 11
B. It teaches how to write macros
C. It explains SQL programming in depth
D. It includes all aggregate query functions

Q5. What is an example of a more complex aggregate query covered in this course?
A. Showing sales totals by state, but only for customers after 1999
B. Creating a simple form
C. Importing customer data from a spreadsheet
D. Deleting old invoices

Q6. In generating a list of products and the lowest cost, what type of aggregate function would you use?
A. Min
B. Max
C. Count
D. Average

Q7. For what purpose is the D-Sum function used in this course?
A. To display the sum of orders for the last 30 days on the main menu
B. To insert new records
C. To send email notifications
D. To export data to PDF

Q8. What is the recommended way to approach learning from this course?
A. Watch the lesson all the way through, then follow along step by step
B. Only read the transcript without watching the video
C. Try to apply the lessons to your own project immediately
D. Skip the beginner levels if you are advanced

Q9. What should you do if you get stuck or do not understand something in the lesson?
A. Watch the video again or use the student forums to ask questions
B. Skip the lesson and move to the next one
C. Search for answers only on third-party websites
D. None of the above

Q10. What is the purpose of building an employee work log in this course?
A. To track employee hours worked between two dates using aggregate queries
B. To manage employee payroll directly in Access
C. To create personalized email reports for employees
D. To schedule employee vacations

Q11. Which Microsoft Access versions are compatible with this course content?
A. 2007, 2010, and 2013
B. Only Access 2013
C. Access 2003 and earlier only
D. Only Access for Mac

Q12. Where can students find more information or ask questions about the lessons?
A. On the student forums at accesslearningzone.com
B. In the Microsoft support forums only
C. By emailing their classmates
D. No questions are allowed

Q13. Which of the following is TRUE about the structure of Richard Rost's courses?
A. They are divided into Beginner, Expert, Advanced, and Developer levels
B. There is only one course level for all students
C. Developer lessons come before Beginner lessons
D. Only Beginner and Expert lessons are offered

Answers: 1-A; 2-A; 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 11, where we focus exclusively on aggregate queries. You might have heard these called totals queries, summary queries, or grouping queries. No matter what name you use, they all do the same thing - they allow you to summarize or aggregate data based on specific criteria.

To start, I explain exactly what aggregate queries are and in what situations you would want to use them. For example, you might need to see your sales totals separated by state. We step through creating some simple aggregate queries to illustrate concepts like sum, average, count, maximum, minimum, and more.

Next, we move on to using complex criteria in your aggregate queries. This includes filtering your summarized data. For instance, if you want to see the total credit limits of your customers grouped by state, but only include customers who joined after 1999, you can do that using the WHERE condition in your queries.

From there, we look at some more advanced examples. One scenario you will work through is finding the lowest cost for each product across several vendors. Since each product may have multiple vendors, and every vendor may sell multiple products, we use a many-to-many relationship to track this information. Then, we generate a query that lists each product with the lowest available price and identifies which vendor offers that price.

Another example we cover is the "last customer contact report." This produces a list of all your customers, the date of the most recent contact, and the type of contact. If a customer has never been contacted, the report will show that too. This tool is extremely useful for tracking which customers have not been reached out to recently and for reviewing your relationship history.

In addition, I show you how to set up an employee work log. We'll design a way to record start and end times for your employees. Using aggregate queries, you can then easily report on the total hours each person has worked within a certain date range, such as seeing their weekly hours. Simply enter the employee, choose the dates, and Access will show you the total.

After the primary lessons, I touch on a number of smaller but important points. These include the expression option in aggregate queries, and how to use the DSum function. For example, you will see how you can display the total orders for the past 30 days on your main menu, and even add a button to refresh this total with a single click. Throughout the lesson, I provide tips and tricks to help you get the most out of these tools.

This class builds on what was taught in Microsoft Access Expert Level 10, where topics like the DLookup function are covered in detail, along with creating quotes and invoices, and using calculated table fields. Since DSum is very similar to DLookup, having experience with Level 10 will make today's material much easier to follow. If you have not yet gone through Level 10, I highly recommend you do so first, and all the details and links are available on my website.

The course content was created using Microsoft Access 2013, but everything taught will apply equally well if you are using Access 2007 or 2010. If you are working with Access 2003, check out my earlier course, Access 221, which covers much of the same material using that version.

In my curriculum, there are four main tracks: Beginner, Expert, Advanced, and Developer. The Beginner series explains the basics of Access. The Expert series, which you are taking now, is aimed at users with solid prior Access knowledge. The Advanced lessons focus on topics like macros and automation, while the Developer series deals with Visual Basic programming in Access. Each track is split into numbered levels. For example, the Beginner lessons range from Level 1 through Level 9.

Apart from my standard courses, I also offer specialized seminars on topics such as web-based databases, calendar-style forms and reports, database security, handling images and attachments, work order processing, accounts payable tracking, learning SQL, and much more. All the details about these seminars are on my website as well.

If any questions come up about today's material, you can post them to the student forums. If you are watching the video from my online theater or using my custom video player, the forum appears beside the class videos so you can read what others have asked and see my answers and the discussion between students. I encourage you to participate, and if you are not online at the moment, you can always visit the forums later at accesslearningzone.com/forums.

To get the most value from this course, my advice is to watch each lesson straight through the first time without pausing to follow along. This lets you get the big picture. Then, rewatch the lesson and work along with me step by step, building the same sample database I use in the video. Don't try to apply these techniques to your own projects until you are comfortable recreating the examples from class. If you run into trouble or something is unclear, watch the lesson again or reach out in the forums. Most importantly, stay open minded - Access can seem complicated at first, but with some practice, you will find it becomes quite intuitive.

Here is what you can expect across the lessons in this level:

Lesson One introduces aggregate queries and walks through basic examples so you understand what they are and why to use them.
Lesson Two shows how to set up aggregate queries with complex criteria and filtering.
Lesson Three uses aggregate queries to break down sales data by month, allowing you to see totals, averages, and count, all grouped by month.
Lesson Four applies minimum aggregate queries and DLookup to generate pricing reports for products from multiple vendors, helping you always find the lowest price source for each item.
Lesson Five covers creating a report listing every customer, the date of their last contact, and details about the contact.
Lesson Six builds an employee work log system so you can track time in and out for staff, calculate hours worked, and filter these records by employee and dates.
Lesson Seven explains miscellaneous items including how to use expressions in aggregate queries, displaying the sum of sales in the last 30 days on your main menu using DSum, making a refresh button, and assigning homework for the next class.

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

What aggregate queries are and when to use them
Building simple aggregate queries
Aggregate query functions: sum, average, count, max, min
Complex criteria in aggregate queries
Using the WHERE condition in aggregate queries
Grouping sales totals by month
Finding the lowest product cost by vendor
Generating a last customer contact report
Building an employee work log with aggregate query
Calculating employee hours worked between two dates
Expression option in aggregate queries
Displaying orders for the last 30 days using DSum
Creating a manual refresh button for DSum values

Article

Welcome to this in-depth guide to aggregate queries in Microsoft Access, also known as summary queries, grouping queries, or totals queries. Aggregate queries allow you to analyze large amounts of data by grouping records and performing calculations on those groups. For instance, you can quickly answer questions like "What are the sales totals for each state?" or "What is the average order amount per customer?" Without aggregate queries, summarizing this information would require extensive manual work.

Let us start by understanding what aggregate queries are and why you would want to use them. An aggregate query groups your data based on the fields you specify and then performs calculations on those groups. The most common calculation functions include Sum, Average, Count, Minimum, and Maximum. For example, suppose you maintain a table of sales, with each record including a customer, an amount, and a state. By building an aggregate query, Access can group the data by state and add up all the sales amounts for each state, showing you total sales per state in a concise table.

To create a simple aggregate query, open the Query Design window and add your table. Select the fields you want to group by, such as State, then add the field you want to perform a calculation on, like SaleAmount. On the ribbon, click the Totals button, which adds a new row in the query grid labeled "Total." By default, all fields are set to "Group By." Change SaleAmount's "Total" row to "Sum." When you run the query, the result displays each state alongside the total sales from that state.

Access offers several aggregate functions. Aside from Sum, you can use Avg (Average), Count (number of records), Min (Smallest value), and Max (Largest value). Switching the function for your value fields will show the different summaries for each group.

Often, you will want to get more specific. Suppose you want to see the credit limits for customers by state, but only for customers who joined after 1999. Start by grouping by State, then set the aggregate function for CreditLimit to Sum or Avg, as needed. You can include more complex conditions by entering criteria within the query. For example, if your CustomerSince field tracks when each customer joined, put >=#1/1/2000# in the Criteria row under CustomerSince. You can also use the "Where" option in the Total row to apply criteria to fields instead of grouping or aggregating them.

Let us look at another example that demonstrates more advanced grouping: finding the lowest cost for each product, considering that each product might be purchased from multiple vendors at different prices, and each vendor might supply multiple products. If you have a Products table and a Vendors table linked by a join table (such as ProductVendors), your query will group by ProductID and use the Min aggregate function on Price to see the lowest available price for each product. But you often want to know not just the price, but also which vendor offers it. To do this, use a DLookup function in a calculated field to retrieve the VendorID from the ProductVendors table where the product matches and the price is equal to the minimum found in your aggregate group.

The DLookup function works like this:

BestVendor: DLookup("VendorID","ProductVendors","ProductID=" & [ProductID] & " AND Price=" & [MinOfPrice])

Here, MinOfPrice is the calculated field showing the lowest price per product. This combination lets you get both the value and the related record.

Another common business scenario is creating a "last customer contact" report. Suppose you want to see, for each customer, the last time you spoke with them and what was discussed. Assuming you have a Contacts table linked to Customers by CustomerID and with a ContactDate field, build a query grouping by CustomerID with the Max function on ContactDate. To include the contact note from that date, use DLookup to retrieve the Note field where the date matches the latest contact:

LastContactNote: DLookup("Note","Contacts","CustomerID=" & [CustomerID] & " AND ContactDate=#" & [LastContactDate] & "#")

This helps you quickly find customers who have not been contacted recently, or scan the summary of your recent discussions.

Let us say you want to track employee work times. Create a table with fields for EmployeeID, TimeIn, and TimeOut. To get total hours worked by each employee in a given period, build a query grouping by EmployeeID, use the Sum function for a calculated field (TimeOut minus TimeIn, perhaps divided by 60 to convert minutes to hours), and put criteria for the date range you are interested in. You can create a simple form with combo boxes and date pickers to let users choose the employee and date range, then run the query to display the results.

In addition to the core aggregate functions, Access offers the Expression option in totals queries, letting you perform calculations like "[UnitPrice]*[Quantity]" inside your grouping. You can also use aggregate domain functions like DSum outside of queries, for example to create a summary box on your database main menu showing total orders in the last 30 days. Place a textbox on your form and set its Control Source to:

=DSum("OrderTotal","Orders","OrderDate>=Date()-30")

You can create a refresh button to recalculate this value by adding a button to the form and attaching this VBA code to its Click event:

Private Sub btnRefresh_Click()
Me.Recalc
End Sub

Aggregate queries are a cornerstone of working efficiently with Microsoft Access. They let you dig into your data and answer complex questions in seconds. With practice, you will be able to group, filter, and calculate summaries for any data scenario your business encounters. If you find yourself confused or want more information on a topic, remember that there are forums and resources dedicated to solving Access problems, and it is always helpful to check there or experiment with simple examples until you master each concept.

With these aggregate queries in your Access toolbox, you can analyze sales by month, find top and bottom performing items, see customer interactions at a glance, and summarize hours worked by staff. Keep experimenting with different combinations of grouping fields, aggregate functions, and criteria to unlock the full power of your data.