Subquery
By Richard Rost
10 months ago
List Customers with Above Average Orders in Access In this Microsoft Access tutorial, I will show you how to use subqueries to list customers with above-average orders. You'll learn to calculate average order values and apply these to filter out larger orders efficiently. This session offers expert-level insights, ideal for those seeking to enhance their querying skills without delving into programming. Riley from Madison, Wisconsin (a Gold Member) asks: How can I generate a list of my customers who have placed above-average orders? For example, if the average order size in my system is $100, I want to see which customers have placed orders larger than that. What's the best way to do this in Microsoft Access? MembersThere 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, subquery in Access, above-average orders query, Access subquery tutorial, querying customers with high orders, Access aggregate queries, Access Order Summary, Access SQL functions, Access query criteria, D average function, SQL average function, Access performance optimization, TechHelp Access tutorial
Subscribe to Subquery
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 going to learn about subqueries. They're basically queries inside other queries, and you can use them to do really cool things. Today, we're going to list all of the customers that have above-average orders. So you take all the orders to figure out what the average one is. That's one query, right, with the average. We just learned about that a couple of days ago.
And then having that value, we can run another query that'll figure out, give me a list of all the orders or all the customers that have orders above that value. And you can put the two of those things together into one query. That's what subqueries are. I'm going to learn more in just a few minutes.
Today's question comes from Marillyn Madison, one of my platinum members. Marilly says, "How can I generate a list of customers who have placed above-average orders? For example, if the average order size in my system is a hundred dollars, I want to see which customers have placed orders larger than that. What is the best way to do this in Microsoft Access?"
Now I'm going to consider this an expert-level video. What is expert? Well, for me, that's sandwiched between beginner and developer. So you don't need VBA code to do this. You don't have to be a programmer, but it's a little bit beyond the basics.
So let me show you a few things you should know first. You should definitely know how Access query criteria works. This is a beginner topic. I'd like you to watch my video I did a couple of days ago on D average. It's one of the domain aggregate functions like the lookup or DMAX. The average lets you take the average value of a bunch of records.
I'm going to show you how to do it first with the average, which is the easy way. Then I'm going to show you some queries which are a little bit more difficult, but they work a lot better. You'd be better off if you understood this first. You'll need to know some basic SQL, real simple stuff, but go watch this first if you don't know anything about SQL. You should understand how aggregate queries work.
I'm going to use my invoicing database, which is part of my TechHelp free template. Go watch this so you understand how my order entry system is built. I already have orders and order details and invoices and customers and contacts and all that good stuff. These are all free videos. They're on my website. They're on my YouTube channel. Go watch all this stuff then come on back and we'll talk about subqueries.
All right, here I am in my TechHelp free template. This is a free database. You can get it off my website if you want to. In here, I have customers. Customers have orders. Orders have order details. You add all these line items up, and then you get the total for each order. I have a query out here called Order Summary Q. This basically takes the orders and the order detail Q, which has the line item totals. It's an aggregate query that sums them all up and that's how you get the total for each order right there.
Okay, now if I want to figure out what the average of all of these orders is, there's a couple of ways you can do it. You can use the D average function, which let's just throw it here on the main menu design view. We'll come over here, we'll say average order value. We'll open this guy up. We'll change the name to average order value. This will now be currency and in here. I'm going to zoom in so you can see better Shift F2. Well, my zoom window is huge. I'll maybe size that.
All right, this will simply be equals the average of the order total from the Order Summary Q. No other parameters. I'm going to look at the entire table or query. Hit okay. All right, let's save it, close it. Reopen it, and there's my average order value. If you look at all your orders, yeah, that seems a lot, right?
Okay, so now I want to make a query that has just the orders over that. Now I could cheat, and I can if this value is already open. I could just put a criteria in the query here, but we don't want to do that. We want to learn how to do this the right way instead of having to rely on a form field. Don't get me wrong, this works in a pinch. You could do this, but there's better ways, and we're here to learn some stuff.
So let's take this data and pull it into another query and then use the D average in there. Let's try that. So create query design. I'm going to bring in the Order Summary Query like that. Let's just bring in the customer ID, the order ID, and then for the order total, down here for my criteria, I want to see just the orders over that D average, and you know what? You could put the same thing in here. I want to see greater than D average of the order total from the Order Summary Q, so it's going to run that D average and use that value as the query criteria for this column.
Right, I'll save this as, I know, Orders Over Average Q, whatever you want to call it. Yeah, I know I violated my rule. It's supposed to be singular. Okay, I break the rule once in a while. It's okay. Actually, it's not okay because if I go to use this in the future and I'm typing a code, I'll get confused, but anyways, we run this, and look at that. That works. Those are all the orders, only four of them over the average.
And again, there's nothing wrong with this method. It's nice and simple. It works. But I'll tell you this, it's going to run slow on very large data sets because here's why: for each record in the Order Summary Query, it's got to run that calculation over and over and over and over again. Remember, we try not to use domain aggregate functions in queries or in continuous forms because this will evaluate for each record.
No big deal in this database. I only have like 30 some orders, and it's on my local PC. But if you've got a hundred thousand orders, and you're pulling data over the network or having for it over the internet, this is going to take forever to run, so there's got to be a better way.
Okay, now the better way is to evaluate that value there once and then run the query, which kind of is what you would do if you relied on this form. So how do we do this without relying on a form field value? Well, the answer is a subquery. If we did the same thing with a subquery, the subquery runs once, evaluates that value, keeps it in memory, and then uses that as the criteria for the rest of the query, for the parent query. That's how subqueries work.
All right, so how do you do a subquery? Well, let's try it again. Let's create query design, same thing we just did, bring in the Order Summary Q. Okay, bring in the order ID, the customer ID. Now again, we're going to bring in the order total, but down here, we're going to write a subquery as the criteria. All right, again, I'll zoom in so you can see it. Now this will be an SQL statement and instead of D average, we'll use the actual average function which is an SQL function. So we'll say greater than, now subqueries always have to be inside a parenthesis, so I'll put my parentheses there. All right. We'll go back inside, and it's going to be a select statement.
So select the average of order total from Order Summary Q. It's a standard SQL statement just like we write it, right? Okay, this whole thing gets evaluated first just one time, and then that gets used as the criteria. So let's save this. I'll call this Orders Over Average Subquery Q, so they're both in there for you.
If I run this now, boom, same results, but trust me, I'm not going to build a giant database so I can show you. Trust me, I've been doing this for almost 30, well, over 30 years now. This will run a whole ton faster than this guy will. The other guy, where's the other guy? This guy. This is the slow guy. On this system, they both run the same speed because there are only 30 records.
Okay, so that's what subqueries do. Now if you take a look at the SQL, if you want to look at the SQL in here, all right, this one is just select customer ID, order ID, order total from Order Summary Q where the order total is greater than, and there's my D average. Okay, the other one's SQL. This guy with the subquery in it looks a little more complicated. Everything else is the same except for that there is the subquery. It's got a select average, and you can use sum in here. You can use max. You can use any of the SQL functions right from Order Summary Q, and then this gets evaluated first just one time, and then the rest of the query will run much, much faster.
Okay, if you want to learn more about subqueries, I cover them in Access Expert Level 32. There are subqueries. I teach how to do rank, median, mode. Right, it's difficult to calculate median, but if you know how to do subqueries, it's much easier because you can do that thing in the middle and then use that to feed your results. Right, but there you go. That's how subqueries work. There's lots and lots you can do them. Again, I cover them more in a lot more detail in my full course, but that's enough to get you started. There's a TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Understanding subqueries in Access
Generating list of above-average orders
Using D average function in queries
Creating aggregate queries
Basic SQL for subqueries
Creating Order Summary query
Using subqueries for better performance
SQL statement for subqueries
Evaluating subqueries once for efficiency
Avoiding domain aggregate functions in queries
COMMERCIAL:
In today's video, we're going to learn about subqueries in Microsoft Access. These are queries inside other queries that let you do some really cool stuff. We'll show you how to list all customers with above-average orders using an efficient subquery approach instead of the slower DAverage function. You'll see how to calculate the average order using the Order Summary Query and then apply that value to find orders above average. This method ensures faster performance, especially with large datasets. Also, in today's Extended Cut, we will dive deeper into subquery applications. 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 focus of the video tutorial?
A. Understanding how to build forms in Microsoft Access
B. Learning about subqueries and their applications
C. Creating reports with Access
D. Basic data entry in Access
Q2. What is a subquery in the context of this video?
A. A tool for importing data from Excel into Access
B. A query that sorts a list of customer names alphabetically
C. A query embedded inside another query to enhance data analysis
D. A method of creating database tables
Q3. What is the main example used to demonstrate subqueries in the video?
A. Listing customers based on their geographic location
B. Calculating the total number of invoices in the database
C. Listing customers with above-average order sizes
D. Sorting orders by date and time
Q4. Why is using a subquery preferred over the D average function in large datasets?
A. Subqueries automatically export data to Excel
B. Subqueries are visual and easier to interpret
C. Subqueries execute the average calculation once, improving performance
D. D average is compatible with SQL functions
Q5. What skill level is this video intended for?
A. Beginner
B. Professional developer
C. Intermediate
D. Expert, between beginner and developer
Q6. Which SQL function is used in the subquery example to calculate the average?
A. COUNT
B. SUM
C. MAX
D. AVG
Q7. What potential issue with domain aggregate functions like D average is discussed?
A. They do not work with text data
B. They can be slow on large datasets because they evaluate multiple times
C. They require VBA to function
D. They are incompatible with Microsoft Access
Q8. In creating the subquery, which part of the SQL statement is crucial for it to evaluate first?
A. FROM clause
B. WHERE clause
C. SELECT statement within parentheses
D. GROUP BY clause
Answers: 1-B; 2-C; 3-C; 4-C; 5-D; 6-D; 7-B; 8-C
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 features an exploration of subqueries in Microsoft Access. I'm your instructor, Richard Rost. Subqueries are essentially queries nested within other queries, allowing you to perform complex data retrieval tasks. In today's session, we'll focus on listing customers with above-average order amounts. This involves creating one query to determine the average order value, and another query to identify customers whose orders exceed this average. Combining these into a single query is where subqueries come into play.
This discussion originated from a question by one of my platinum members, Marillyn Madison, who wanted to know how to generate a list of customers whose orders surpass the average order amount, say, more than $100. In Microsoft Access, tackling this problem doesn't require VBA code but is a bit advanced beyond the basics.
Before diving into subqueries, it's crucial to understand Access query criteria. If you're not familiar, I recommend reviewing my previous video on domain aggregate functions such as DLookup or DMax, particularly focusing on DAverage, which helps average a set of records.
We'll start by using the DAverage function to determine the average order size, then move on to more efficient query-building techniques. It's beneficial to have a grasp of basic SQL and understand aggregate queries. If these concepts are new, consider reviewing introductory material on SQL.
Utilizing my invoicing database—a part of the TechHelp free template—can be helpful. This database includes customers, orders, order details, invoices, contacts, etc. Begin by setting up an Order Summary query that aggregates order totals from order details, allowing us to calculate each order's total value.
To find the average order value, you can use the DAverage function in Access. This involves calculating the average directly from the Order Summary Query. Once established, this average allows us to create a second query to list orders surpassing that average. While it's possible to resort to simply using query criteria in Access, our goal is to achieve this without relying on form fields or temporary solutions.
To efficiently handle larger datasets, subqueries become critical. Subqueries allow you to calculate the average value once, store it in memory, and use it in the larger query. This method contrasts with domain aggregate functions that repeatedly calculate results for each record—potentially slowing down your system for extensive data sets.
For a practical example, create a new query design using the Order Summary Q. Bring in necessary fields like customer ID and order ID. When setting criteria for order totals exceeding the average, employ a subquery. This subquery calculates the average of the order totals once, using a standard SQL SELECT statement within parentheses, thus ensuring optimal performance.
For those interested in further exploring subqueries, they are covered in detail in my Access Expert Level 32 course, including applications like rank, median, and mode calculations. Subqueries can significantly simplify such advanced calculations by feeding intermediary results into the main query.
To dive deeper into subqueries and enhance your Access knowledge, visit my website for a full video tutorial with comprehensive, step-by-step instructions. Live long and prosper, my friends.
Topic List
Understanding subqueries in Access
Generating list of above-average orders
Using D average function in queries
Creating aggregate queries
Basic SQL for subqueries
Creating Order Summary query
Using subqueries for better performance
SQL statement for subqueries
Evaluating subqueries once for efficiency
Avoiding domain aggregate functions in queries
|
