Avg, Rank, Median
By Richard Rost
4 years ago
Average, Rank, & Median Values in Microsoft Access
In this Microsoft Access tutorial, I will show you how to calculate the average, rank, and median values of a set of records.
Gary from Muncie, Indiana (a Gold Member) asks: I need to calculate the average and median home values in a particular area. I can't figure out any way to calculate a median in Access. Can you help?
Members
Members will learn how to create a custom DMedian function in VBA using recordsets.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Recommended Course
 Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Average, Median, DAvg, DAverage, DMedian, Median in MS Access, Median in Microsoft Access, ms access median, median income, median home value, rank
Intro In this video, we will learn how to calculate the average, rank, and median of a set of records in Microsoft Access. I will show you several ways to find averages, including using aggregate queries, form and report footer totals, the DAvg function, and VBA. We will also talk about how to assign ranks to your records using a bit of SQL, and I will demonstrate a practical method for finding the median value in your data, even though Access does not have a built-in median function.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to calculate average, rank, and median of a set of records in Microsoft Access.
Today's question comes from Gary in Muncie, Indiana, one of my Gold members. Gary says, "I need to calculate the average and median home values in a particular area. I can't figure out any way to calculate a median in Access. Can you help?"
Well Gary, you are not crazy. Microsoft Access does not have any functions to do median. I know that you can do it very easily in Excel with the MEDIAN function. But as always, Access gets treated like the red-headed stepchild of the Microsoft Office family and there is no median function. So you have to be creative and figure out how to do it yourself. Let me show you some options.
Before we get started, I have a bunch of other stuff I want you to watch first. Today we are going to learn about averages. We are going to cover the DAVG function. That is one of the functions I am going to go over. Watch all or any one of these other ones first. DLOOKUP is the parent of all of these. DLOOKUP lets you look up a value from a table or query. DSUM lets you calculate a sum of a bunch of records, DMAX gives you the max of a bunch of records, DMIN gives you the min, and DCOUNT gives you the count of a bunch of records. These functions are all related. Today we are going to add DAVG to it. But it will help you if you know any of these first.
If you have time, go watch one of these, especially DLOOKUP first. If you do not know any of this stuff, I am going to be doing pretty much all of this in today's class: query criteria, parameter queries, the BETWEEN keyword to find a date (like, for example, between two dates), aggregate queries (which is grouping and summing and averaging in queries), putting totals on the bottom of forms, and a little bit of VBA - just a couple lines of code, nothing crazy.
Watch as much of this as you can if you can when you can. I am going to show you everything in this video. But if you have questions, if you do not understand something I am doing, refer to these videos. These are all free. They are on my website and my YouTube channel. Go watch them. You will find links. Click down below in the description below the video window.
Also, to calculate our median and rank, we are going to have to use a bit of SQL. Go watch this video too if you have never done SQL before. Very good video. Go watch that - again, free.
Let's start by creating a table with some sales data in it. I know, Gary, that you are doing home values, but let's keep it easy. Let's say we are doing sales amounts. We have a SaleID, a SaleDate perhaps, and an Amount. We are keeping it simple. This is my SaleT or sales table.
Now, let's put some sample data in here. Let's put a couple of dates from January: $10, $20, and $30. From February, we have $50 and $70. If you have not noticed, I am using the ISO date standard: year-month-day. I have a video that explains that too. I will put a link down below. $220, let's say $100, and a couple from March - let's put $25 in there.
So, we have some different sales from some different months: January, February, and March. That one should be March. There we go.
If you want to take an average of this, the easiest way to get averages is to simply use an aggregate query. Here is the table. Let's close and save this. Go to Create > Query Design and bring in that sales table. Let's bring in the Amount. Go to Totals, and then we will change this to Average. See, there is Average, Min, Max, Count, Standard Deviation, a bunch of stuff. Do you see a Median down there? No. Come on, Microsoft, add a Median to that. Then run it, and there is your average of all of those sales.
If you do not want all of them, if you want to say only a group of them, turn off that aggregate (the Totals). There they are again. If you want to show only the sales between two dates, for example, like all of February's sales, in the criteria row down here, use: Between 2/1 and 2/28. When you run it, those are the records.
You cannot just aggregate it here because then it breaks each one of these down. So you have to feed these results into another query. There is that. Save this as SalesByDateQ. Then pull that into another query: Create > Query Design, bring in SalesByDateQ, bring in Amount, then Totals, then calculate that, and run it. There is your average for the records that show up in SalesByDateQ (in this case, February). Save this as AverageOfSalesQ or whatever you want to call it.
If you want the user to type in those dates instead of hard coding them, you can use a parameter query. You could say: Between [Start Date] And [End Date]. This is all stuff I covered in my other lessons, those other videos I told you to watch. Enter Start Date: 2/1, End Date: 2/28, and there you go. That will run if you open up AverageOfSalesQ, because it has to first run that other query. Enter 2/1, 2/28, and there we go.
That is using an aggregate query.
You can also put totals in the bottom of a form in the form footer. Let's say I have a continuous form. I will just copy and paste the one I have. Copy, paste. We will call this MySaleF or MySaleForm. It is empty. Go to Design View, bind this to your Sale table, and delete the stuff that is in here. Add existing fields: bring in SaleDate and Amount. Delete the labels for now. There is SaleDate, there is Amount.
Save that and close it. That is what it looks like.
Now we can put totals down in the bottom, right down here. I am going to copy these controls. Actually, I will just copy this one. In here, open the property sheet. Go to the Data tab. The control source will be =Avg([Amount]). The name of the box can be avgAmount or whatever you want to call it. Same thing you can do with Sum or any of those other functions, right down here. You can make this =Sum([Amount]), sumAmount. These are form footer totals - as I said in another video I wanted you to watch.
Now when we open that up, if you see #Name?, that means Access does not recognize that function. I made a mistake, and I do this all the time. I am going to leave this mistake in the video because I seriously make this mistake constantly. In Excel, you have to type out the word AVERAGE like that - that is an Excel function. I flip back and forth between Excel and Access all day long. In Access, it is AVG, and you cannot use AVG in Excel. I make this mistake continuously. You probably will too. If you see #Name?, just flip them. There is my average and there is my sum.
Now, this average and sum work because they are looking at all the records above. If you filter this list, for example, right click and go Date Filters > Between, oldest date 2/1, newest date 2/28, and click OK, these calculations will update based on whatever is above. So it is another easy way to get averages, sums, counts, maxes, and mins, but not medians. Come on, Microsoft.
So, that is the regular average function.
We have seen how to do it in a query and how to do it in a form footer total.
There is yet another way you can do it anywhere you want, even if you are not in a form that has that data or a query, if you just want to grab a value. Let's say you want to put your average sale value on your main menu. For that, we use the DAVG function.
Let's change this box here to say Average Sales. The format is going to be Currency, and the Name is avgSales. What is the control source going to be? Let me zoom in so you can see this better, Shift+F2. It is going to be:
=DAvg("Amount", "SaleT")
What does the D mean? It means Domain, like DLookup, DSum, DCount, DMax. It says you are going to look this up in a different table or query. Since we do not have a table or query at all in the main menu, it is just going to go out and find it. Versus Avg by itself, you have to be on that set of records, like in a form footer. But DAvg says, I want you to average the Amount field from the SaleT table.
Now, if I close that and open up my main menu again, there is my average using the DAvg function.
Want to add criteria to it? You can throw a criterion in there, just like any D function, e.g.,
=DAvg("Amount", "SaleT", "SaleDate > #" & Date()-365 & "#")
That will give you the average amount from sales in the past year. Or you could put:
=DAvg("Amount", "SaleT", "SaleDate Between #2/1# And #2/28#")
Just be careful because if you use 3/1, you might get March 1st sales. Watch my Between video. I talk all about using Between vs. using an inequality, and why you have to be careful, especially if you have times in your order dates. I have a whole video on that.
That is using a function there.
There is one more way you can do it. I said I would show you a little VBA.
Let's put it down here in this message box. This is called my status box, and I create this in the Blank Template video where I show you how I built this template. Go watch it if you get a chance.
We are just going to change this. Instead of Hello World, I am going to message the result in this box, the status box. Right-click on the button and go to Build Event. That puts me in my VB code builder. Here I am.
I am going to declare a currency value called c: "Dim c as Currency". I am going to say c = DAvg("Amount", "SaleT"). You can put criteria in there if you want to. Then I will say status "Average sales is " & Format(c, "Currency"). Status is my own function that just puts that in the text box up top.
Let's see what that looks like. Now click the button. There you go.
That is four different ways to do the average: in a query, in a form footer total or a report footer total (they work the same way), with the DAVG function as a control source in a text box, or in your VBA code.
So that is average.
So what about median? Again, median is not given any love by Microsoft. You cannot do median by itself in Access. There are two ways we can do median. One is the kind-of-sort-of easier way that I am going to show you and it takes just a couple of minutes to implement. It is not perfect, but it works. I say it is not perfect because if your need for a median is scientific or financial and you have to have exact figures, this will not always work for you. But if you just need a rough number (for example, 5,000 houses and you want to find roughly the median value), or you have a whole bunch of people and want the rough median, it is good enough. It is good enough for average usage. Again, if you need a scientific number, I am going to show you how to write a function that gets you the exact scientific median in the extended cut. That is going to require a lot of VBA, but we will cover it. For the rest of us who just kind of want to see a median, this will work.
The reason why I say it is kind of sort of maybe, and that it is not exact, is because with a median, if you have an odd number of items, you have a median - one in the middle. If you have an even number, you have to take the two middle numbers and average them. This does not do that - it just gives you one of those two numbers in the middle. If you have a very large set of data, it is fine, but again, it is not exact. It is not scientific. Let me show you how it works. Again, if you need a super-serious median function, we are going to put one together in the extended cut for the members. But what I am going to show you now is good enough for most needs.
Create a new query and bring in the sales table. Bring in your list of items and sort this list descending. There is our list. To keep this simple, let's change the values to 1, 2, 3, 4, 5, 6, 7, 8, 9. We have nine items (an odd number), so our median should be number five.
In order to figure out the median, we have to assign each one of these items a rank: what number, 1 through 9, does each fit? Here is how you do that. It involves a little SQL.
We are going to create a calculated field. The rank of each item is going to be:
(Select Count(*) From SaleT as S1 Where S1.Amount < SaleT.Amount)
Let me explain what that means. In a basic SELECT statement, you have SELECT field FROM table WHERE condition. Here, we have to compare the sales table to itself. I want to see how many items in the Sales table have an amount less than the current item's value. We are aliasing SaleT as S1. In memory, we essentially have two tables: S1 and SaleT. This says, "give me the count of how many items from the same table have an amount less than the current record's amount." I know it is complicated, but that is what it is. You just have to type that in or copy it.
Do not forget your parentheses.
Run it. It is saying how many items are less than 1 (none), less than 2 (one), less than 3 (two), and so on, so you get a rank. If you have a couple of items that are the same, this recordset is not updateable. Save this as MyRankQ.
As soon as you put this calculation in there, this query is not updateable anymore. I have a whole video on why stuff is not updateable. Go watch that too. But you can go back to the Sales table and make changes.
If you have a couple of five-dollar items, for example, you will get 0, 1, 2, 3, 4, 4, 4, 4 because you have four items tied for fourth place, and then 8. Again, this is why it is not super scientific, but it works most of the time.
Put those values back: 1, 2, 3, 4, 5, 6, 7, 8, 9.
Now, since this is going to be an integer starting at zero and counting up, to find the median, basically what you have to do is say, give me the highest value of Rank and divide it by 2 - there is your median. Again, this is why it is not super scientific, because if you have an even number, it will just be one of those two. It may or may not be exactly correct, but it is close enough.
How do we do that? Leave RankQ as it is, copy and paste it as MyMedianQ. In MyMedianQ, in the criteria row for Rank, put:
Int((Select Count(*) From SaleT)/2)
In other words, select the count from SaleT and divide it by two. If there are eight records, dividing by two gives you four, which will give you the median record. Save and run it. When Rank is four, that is the median record: $5. If we change the data, say add another one (making it an even number, 1 through 10), you get a new median. Again, it will be one of the two middle items, usually the larger one.
Now, I know some of these functions seem kind of crazy. I spent a lot more time explaining them in my Access Expert Level 32 class. This is definitely more on the expert side - it is a pretty crazy function.
I will be honest with you, I do not remember this function by heart. It is not stored in my encyclopedic brain, and I remember lots of crazy stuff about right records and all kinds of things. But not this function. If it is not something you use every day, you are not going to remember it. But I do know where to find it - Access Expert Level 32. I searched for median on my website, and I remember I covered it in that class. I actually watched my old video to refresh my memory, because a lot of you have been asking me how to calculate medians.
In that video, I spend a lot more time calculating rank, median, and mode. If you need to learn mode, that is a whole different ballgame.
So now, like I said, this function will get you by. If you do not need something super scientific, if you are not basing someone's mortgage on it, this rough median is great. If you need an exact median function, I will build that in the extended cut for members. Silver members and up get access to all of my extended cut videos (there are lots, over 200 now). Gold members get access to download the databases I build and my Code Vault, where this DMedian function will soon be available. You can pick through all the stuff in there, with links to most videos where I create these functions. Membership is cheap, it is worth it. I hope you learned something, and we will see you next time.
How do you become a member? Click on the Join button below the video. After you click Join, you will see a list of all the different membership levels that are available, each with its own perks.
Silver members and up get access to all of my extended cut TechHelp videos, one free Beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free Expert class each month after you finish the Beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full Beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website.
Do not worry - these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What is one key reason why calculating a median is challenging in Microsoft Access?
A. Access does not have a built-in MEDIAN function
B. Medians can only be calculated with DAVG
C. Access only supports medians for numeric fields
D. The MEDIAN function is hidden in advanced settings
Q2. Which function in Access allows you to calculate the average of a field?
A. DSUM
B. DAVG
C. DMIN
D. DMAX
Q3. Where can the AVG function be used to display totals in Access?
A. Only in tables
B. In form footer or report footer totals
C. Only in VBA
D. Only in macros
Q4. If you want to show an average for a filtered set of records in Access, which tool would you use?
A. A table view
B. An aggregate query with Group By and Average
C. Macros
D. The Relationships window
Q5. How does a parameter query improve user experience when calculating averages for certain dates?
A. It automatically finds the maximum value
B. It asks the user for input values at runtime
C. It locks the records for editing
D. It causes errors in the query
Q6. What is the purpose of the DAVG function in Access?
A. To create a new table with averages
B. To calculate an average from any table or query, regardless of where it is used
C. To run update queries for large data sets
D. To define defaults for new records
Q7. What keyword would you use in a query criteria to find records between two dates?
A. COMPARE
B. ONLY
C. BETWEEN
D. INSIDE
Q8. Which common mistake did the instructor mention regarding the use of average functions in Excel vs Access?
A. Mixing up the syntax of AND and OR
B. Using the word AVERAGE in Access instead of AVG
C. Using DLOOKUP incorrectly
D. Using BETWEEN instead of EQUALS in Access
Q9. Which approach is NOT suitable for calculating the median in Access?
A. Using the aggregate functions drop-down in a query
B. Using DAVG with criteria
C. Using a calculated field with SQL and ranks
D. Writing a custom VBA function
Q10. What is the conceptual definition of "rank" when calculating median in Access using SQL?
A. The number of duplicate records in the table
B. The count of records with a value less than the current record's value
C. The average of all record values
D. The maximum value in the table
Q11. Why is the median calculation method shown in the video considered "not super scientific"?
A. It uses the wrong field for ranking
B. With an even record count, it does not average the two middle values but picks just one
C. It ignores all records except the largest
D. It only works on text fields
Q12. What does the instructor recommend if you need a truly scientific calculation of median in Access?
A. Use the quick method and hope for the best
B. Write a longer VBA function for exact results
C. Use Excel exclusively
D. Download third-party add-ins
Q13. How does the DAVG function differ from the standard AVG function in Access?
A. DAVG only works in queries with parameters
B. DAVG can be used anywhere to reference any table or query, AVG needs a set of records
C. DAVG returns text values
D. There is no difference
Q14. Which of the following is NOT one of the four ways shown to calculate averages in the video?
A. Aggregate query
B. Form or report footer total
C. DAVG as a control source
D. Calculated Excel column imported into Access
Q15. According to the video, what should you do if you receive the "#Name?" error when using average functions in a form footer in Access?
A. Remove all filters from the form
B. Check the function name to ensure you used "AVG" instead of "AVERAGE"
C. Delete all form controls and start over
D. Reinstall Microsoft Access
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 9-A; 10-B; 11-B; 12-B; 13-B; 14-D; 15-B
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 covers how to calculate averages, rankings, and medians in Microsoft Access. The question for this lesson comes from a student who wants to know how to find both the average and median of home values in a particular area, but has run into a challenge since Access does not provide a built-in median function.
First, let me clarify that you are not missing anything: Access really does not have a median function, unlike Excel where you can find it easily. This is a known gap in Access, so you have to get creative if you want to calculate the median.
Before we jump into solutions, let me suggest that if you are not already familiar with domain aggregate functions like DAvg, DLookup, DSum, DMax, DMin, or DCount, it will help to review them. DLookup is foundational, allowing you to retrieve a value from a table or query. DSum, DMax, DMin, and DCount allow you to get the sum, maximum, minimum, or count from a set of records, and today we will also look at DAvg for averages. Becoming comfortable with these functions is very helpful for understanding the material in this tutorial.
You should also have some experience with query criteria, parameter queries, the BETWEEN keyword for filtering dates, and aggregate queries for grouping and calculating totals like sums and averages. I will also touch on putting totals in the form footer and show a little VBA code, though nothing complex. All of these concepts are covered in more depth in other free videos available on my website and YouTube channel. If something in this video is unclear, I encourage you to look into those resources.
For calculating median and rank, you will need to use a bit of SQL as well. If you have never worked with SQL in Access, watching an introductory video is a good idea.
Let us begin by building a simple table with some sales data. While the original question was about home values, to keep things straightforward, I will work with sales amounts. Suppose you have a SaleID, SaleDate, and Amount in your table, which I am calling SaleT. With some sample data entered for sales in January, February, and March, you are set to proceed.
Calculating an average is very straightforward in Access using an aggregate query. Bring your sales table into a new query, add the Amount field, and use the Totals function to change the calculation to Average. Run the query, and you will see the overall average of your sales amounts. If you want to look at only a subset, such as sales between two specific dates, filter by entering a criteria (like BETWEEN 2/1 AND 2/28) in the criteria row. To calculate the average for just those filtered results, create another query based on this one and again use the Totals function to average those results. You can also use a parameter query so users can enter their own dates each time the query runs.
Another useful approach is to add totals to the bottom of a form. In the form footer of a continuous form, you can add a text box with the control source set to =Avg([Amount]) to show the average of all the visible records. If you want a sum, use =Sum([Amount]). These totals automatically update when you filter the records in the form. This method works for a variety of calculations like sum, count, max, and min, but, unfortunately, still does not handle median.
If you want to display the average in a place where you do not have the records available in the form or report, such as on a main menu, you can use the DAvg function. For example, you can set the control source of a text box to =DAvg("Amount", "SaleT") to show the average of the Amount field in your sales table. If you want to restrict the calculation to a specific time period or condition, you can add a criteria to the function, for example, =DAvg("Amount", "SaleT", "SaleDate > " & Date()-365) to get the average from the last year. You can use the BETWEEN operator here as well, but be mindful of how dates are stored and potential pitfalls if your dates include time values.
Yet another approach is to use VBA code to calculate and display an average. For example, by declaring a currency variable, using the DAvg function to assign the calculated value, and then setting a status function to display it on your form, you can show the average dynamically through a button click. This is helpful if you want more control over how and when values are displayed.
Now, turning to the median: Access does not include a built-in median function, so you have to be a little inventive. I will show you a common workaround that is straightforward to implement and is suitable for most general purposes, though it is not perfect for scenarios that require scientific exactness. Specifically, this method works well when you need a rough median, such as in large datasets where being off by one record does not matter. For situations that require precise calculation, such as financial or scientific analysis, I will explain how to build a more exact function using VBA in the Extended Cut of this class.
The main limitation of this quick method is that it does not handle the even-number-of-records scenario perfectly, since a proper median calculation should average the two middle values and this approach only returns one of those values. Still, for large and general datasets, it is good enough.
To set this up, create a new query using your sales table. In this query, you will create a calculated field to assign a rank to each record. This involves a bit of SQL: you write a subquery that counts how many records have an Amount less than the current row, essentially ranking each value. Sorting your data and adapting this method allows you to identify the median position, especially if you use a simple test dataset with a known odd number of values so you can see the process in action.
Once each record has a rank, finding the median is a matter of selecting the record in the middle position. You can do this by taking the total number of records, dividing by two, and using that value as criteria for Rank. This does not average the two middle records if your dataset has an even number of items, which is why it is not precise, but it does point you to the approximate median value.
Be aware, though, that when you use these ranking calculations the resulting query may no longer be updateable. If you want to change your data, you must do so directly in the base table.
This method is a little advanced and you probably will not remember the SQL syntax by heart unless you use it often, but you can always refer back to my detailed video tutorials for it. In addition to median and rank, I cover mode calculations and other statistical functions at greater depth in my Access Expert Level 32 class.
To sum up: calculating an average in Access can be done in several ways including aggregate queries, form/report footer totals, domain aggregate functions like DAvg, and VBA code. Calculating the median can be approximated through clever SQL tricks, or done more precisely with a custom VBA function (which I cover in detail in the Extended Cut for this lesson).
If you are interested in becoming a member to access more in-depth content, there are several membership levels (Silver, Gold, and Platinum), each with their own set of perks like extended cut videos, downloadable databases, code libraries, and more. Membership gives you access to a wealth of resources and answers to your questions.
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 Creating a sales data table in Access
Entering sample sales records
Building an aggregate query for averages
Using the Totals button in queries
Filtering query results between two dates
Saving and chaining queries for custom filters
Creating parameter queries for user-defined date ranges
Adding average and sum calculations to form footers
Using =Avg([Amount]) and =Sum([Amount]) in forms
Understanding Name? errors between Excel and Access functions
Applying filters dynamically to form totals
Using DAvg to calculate averages outside of queries and forms
Adding criteria to DAvg function
Formatting DAvg results as currency
Calculating averages with VBA and DAvg
Displaying VBA-calculated results in a status box
Assigning rank to records using a subquery
Calculating rank with SQL to compare field values
Creating a query to assign ranks for each record
Calculating a rough median value from ranked records
Using the INT function and subquery to identify the median record
Understanding the limitations of this rough median approach
Handling duplicate values in median calculations
Adjusting calculations for odd and even recordsets (median)
Saving and running chained queries for median result |
