DAvg
By Richard Rost
9 months ago
Calculate Average Customer Worth Using DAvg
In this Microsoft Access tutorial, I will show you how to calculate your average customer worth using the DAverage function. We'll explore an aggregate query to sum customer orders and discuss why understanding average customer value is crucial for business decision-making. Learn how to implement this function effectively without needing advanced programming skills.
Vanessa from Mesa, Arizona (a Platinum Member) asks: What's the easiest way to calculate my average customer worth?
Members
There is no extended cut, but here is the file download:
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, DAvg function, calculate average customer worth, aggregate query, domain aggregate functions, AVG in Access, foreign key customer ID, statistical functions Access, currency value calculation
Subscribe to DAvg
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today, we're adding to the D family. We're doing DAverage. I have videos on DLookup, DMax, DMin, DCount, DSum, why you shouldn't use DFirst, DLast, and lots of other related stuff. Today we're covering one I haven't covered yet, and it's DAverage. And guess what? You can use it to take an average of some stuff.
Vanessa from Mesa, Arizona, asked a very simple question: What's the easiest way to calculate my average customer worth? It's a very important metric for a business to have. Vanessa, it's important. I know what my average customer worth is because if you have ad spend, you know if, let's say, your average customer is worth $20 and it's costing you $25 in ad spend, that's no good. You can't do that. So let's figure it out.
This will be what I call an Expert-level video. Expert is sandwiched between Beginner and Developer, so you don't need to know the programming stuff. You don't have to have any VBA to do this, but it's a little more advanced than the beginners. So if you're just starting out, maybe finish my Beginner course first. Definitely go watch my DLookup video first. DLookup is like the granddaddy of all the D functions, DMax, DMin, whatever. I've got videos for all of them pretty much. But this is the one that started it all. This is the easiest one to get. It lets you look up one value from one record.
Now, I already have an order entry system built, so I have customers and orders, and I can calculate their average worth from that. If you want to learn how I built that whole thing, go watch this. It's free. You can watch the whole thing: customer building and building all the invoices and the order entry system. So we're on the same page when I get to actually calculating the worth you know where I got all this stuff from.
On top of that, I'm going to use an aggregate query to calculate the total of each customer's orders. So if customer number one has six orders, it's going to add those all up in an aggregate query for all of the customers. These are all free videos on my website or on my YouTube. Go watch those, and then come on back, and you can see it right here. The order sum by customer. That's exactly what I'm calculating in the aggregate query.
In here, I got customers. Customers have orders, orders have details. We take these order details, we add those all up to get an order total, and then I've got some queries over here, like my order summary query, that takes each order and sums up the items on each order. So the order total for order one is $4200. Now you can see here, like customer one has multiple orders. I think he's the only customer that does. I didn't put any other orders in for any other customers. But what we want to do is get rid of the order information from this query, and then we'll have a list of just each customer's total.
So notice customer one is $4200 plus order three there is a 360, so it should be 4560 if I do this right. So I'm going to come in here. This is an aggregate query, and we're going to get rid of all the stuff that is specific to just the order except for the customer ID, obviously because the customer ID is unique. That's basically from the customer table. It's a foreign key, so we'll get rid of the order ID and the order date, and we'll get rid of "is paid."
Now what it's going to do is group by customer ID so every customer will have their own row, their own record, and then it'll add up all of their order totals. And there we go, see 4560, that's exactly what I was looking for. And if all these other people have multiple orders, you'd have one record in here for a customer. Now that I have each customer's total worth, I can calculate my average customer worth by simply averaging that field, that column.
Let's save this. I'm going to save this. I know I'm ruining the order summary query. That's why I'm going to save it. It's still the order summary query. It's okay. Now we can go back to the main menu or wherever you want to put it. We can use DAverage to calculate the average of that field. It's order total on order summary query. So average customer worth. Let's open this guy up. I'll call it right here average customer, and this is going to be a currency value.
The control source is going to be what I'm going to zoom in Shift+F2 so you can see this better. It's going to be equals DAverage. It's AVG. I know it's confusing. You have to remember it's AVG when it's Access. It's AVG when it's average spelled out like in Excel. It's average spelled out. And everybody always gets that wrong in Excel, but here it's DAVG, the average of order total from the order summary query. Now just like the other D functions, you could add criteria here if you wanted to. You can say like where customer ID is whatever you want to have. But we're just doing the whole table, the whole query, so that's all you need right there.
Hit okay. Close it, close it, save it. Open it. Boom, there you go. There's your average customer worth. Let's see if that makes sense. Yeah, see this is why average is a bad number because there's one big order here that threw it off. It's like when they look at the average wealth of Americans. You got a few knuckleheads at the very top with trillions that are throwing everyone else off. That's why median is a much better indicator. There is no DMedian function. That's unfortunate, but I do teach you how to calculate median in this video. I'll put a link down below, as well as links for DMax, DMin, DCount, DSum, why you shouldn't use DFirst, DLast. DZ is another one you're going to find useful with all these domain functions. So yeah, there's lots to learn if you want to learn this stuff.
If you want to learn more about these, I cover them in a lot more detail in my Access Expert Level 29 class. I'll put a link to this down below as well. Aggregate functions here. They are: sum, average, count, maximum, first-last. The domain aggregate functions, we spend a lot more time on all these. Yeah, there's all kinds of cool stuff. Lots more to learn on my website.
But that's it for DAverage. I've covered all of the domain aggregate functions now, all the D functions except there's two left that I haven't done. There's DStandardDeviation and DVariance. I know those are for statistics nerds. I used to be a statistics nerd back in high school and early college, but I haven't done a lot of that lately. If you are a statistic nerd and you want to see some examples on that, post some comments down below. If I get enough people interested, I'll make a video on it. That's how I do it. One of the reasons why I did this video is because I did a search for DAverage and my video didn't show up in the Google results in the top spot. So I had to make another DAverage video. I'm telling all of you right now if you ever do a Google search for anything Access related and I don't show up, I want to know exactly what your Google search phrase was and I'm going to make a video for it. That's my goal. I got to get on there.
That's going to do it though for your TechHelp video for today. Hope you learn something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
DAverage function
Average customer worth calculation
Aggregate query creation
Order sum by customer calculation
Order summary query
Control source setup with DAverage
Difference between AVG and DAVG
Utilizing criteria in DAverage
Consideration of average vs. median values
COMMERCIAL:
In today's video, we're learning about DAverage and how to use it to calculate the average customer worth, a key metric for businesses. I'll show you how to set up aggregate queries to sum customer orders and then apply DAverage for the final calculation. You'll also get to see how to use the AVG function in Access. Plus, I'll explain why using average isn't always ideal and hint at calculating medians instead. In the Extended Cut, we'll explore more on domain aggregate functions, including DStandardDeviation and DVariance. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary function of DAverage in Microsoft Access?
A. To look up a single record from a database table
B. To calculate the maximum value in a dataset
C. To find the average value of a specified dataset
D. To count the number of records in a dataset
Q2. In the video, why is understanding the average customer worth important for Vanessa's business?
A. It helps determine the profit generated from each product
B. It guides the allocation of resources across different departments
C. It assists in evaluating whether the ad spend is justified by customer value
D. It indicates the total number of customers the business has
Q3. What level of knowledge is required to follow the DAverage tutorial mentioned in the video?
A. Beginner level
B. Expert level
C. Developer level
D. No prior knowledge required
Q4. Before using DAverage, which related domain function does the instructor recommend watching a tutorial on first?
A. DCount
B. DSum
C. DMax
D. DLookup
Q5. What type of query is used to calculate the total of each customer's orders in the video?
A. Select Query
B. Crosstab Query
C. Aggregate Query
D. Union Query
Q6. What is the main criterion used in the aggregate query to group orders in the video?
A. Order ID
B. Order Date
C. Customer ID
D. Product ID
Q7. Which domain aggregate function is mentioned in the video as not being available in Access?
A. DAverage
B. DCount
C. DMedian
D. DZ
Q8. What additional domain aggregate functions does the instructor mention as possible future topics based on viewer interest?
A. DCount and DSum
B. DMax and DMin
C. DFirst and DLast
D. DStandardDeviation and DVariance
Answers: 1-C; 2-C; 3-B; 4-D; 5-C; 6-C; 7-C; 8-D
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 TechHelp tutorial from AccessLearningZone.com is brought to you by me, Richard Rost. We're expanding our knowledge of the D family of functions today with DAverage. Previously, I've created tutorials on functions such as DLookup, DMax, DMin, DCount, DSum, and discussed why DFirst and DLast should be avoided. However, DAverage is a function I haven't covered yet, and it's quite useful for calculating the average of a set of data.
Vanessa from Mesa, Arizona, asked about the simplest way to calculate her average customer worth, which is a significant business metric. It's crucial to know, especially when considering advertising costs. For instance, if your average customer is worth $20 but you're spending $25 on ads per customer, this strategy wouldn't be viable. Let's work through this concept together.
This session is designed for those at an Expert level. It fits between Beginner and Developer levels. You don't need VBA knowledge for this, but it's a bit more advanced than the Beginner level. Newcomers might want to complete my Beginner course and watch the DLookup video first. DLookup is essential because it lays the foundation for understanding all D functions.
I've already developed an order entry system, which includes customers, orders, and the capability to compute their average worth. If you'd like to see how this entire system was constructed, you can find videos online for free that explain it all, including customer accounts, invoices, and the order entry system. This foundation is important for our task of calculating customer worth.
We'll utilize an aggregate query to sum each customer's orders. This means taking several orders per customer and adding them in a query that aggregates data for all customers. I have previous videos available that explain how to do this, which you can view on my website or YouTube. With this, we organize the order sum by customer through an aggregate query process.
The data we have involves customers, each of whom may have multiple orders. We sum the detailed order information to attain an overall order total. There are queries like the order summary query that sum up the order contents, giving us totals such as $4200 for the first order. If a customer has more than one order, we aim to identify a combined total across their orders.
We simplify our query to focus solely on each customer's total, removing order-specific details while retaining the customer ID. This transformation results in a query that provides total order amounts for each customer, isolated from individual orders. When done correctly, the total for customer one should showcase values like $4560.
After saving these changes in the query, perhaps modifying the existing order summary query, we're ready to calculate the average worth using DAverage. This calculation will use the order total's values from the order summary query. Assign it a currency type and set the control source for DAVG, which is the appropriate abbreviation in Access.
No additional criteria are needed, unless you're narrowing your focus to specific customer categories. Save and exit once you've done this to see your average customer worth. Although it's a good metric, remember that averages can be misleading if influenced by extreme values. Median values often provide a clearer picture, but Access lacks a direct DMedian function. However, I offer a tutorial on calculating median values separately.
I invite you to check out my Access Expert Level 29 class for more thorough insights into these topics. In this class, you'll find detailed examinations of domain aggregate functions like sum, average, count, and others.
This session completes our exploration of the D functions. Only DStandardDeviation and DVariance remain untouched, useful for those into statistics. If that's you, let me know, and I might craft future tutorials on these topics based on your interest.
This concludes today's TechHelp tutorial. For a complete video demonstration with step-by-step guidance on everything we've discussed, visit my website through the link below. Live long and prosper, my friends.
Topic List
DAverage function
Average customer worth calculation
Aggregate query creation
Order sum by customer calculation
Order summary query
Control source setup with DAverage
Difference between AVG and DAVG
Utilizing criteria in DAverage
Consideration of average vs. median values
|
