Sum of Last Orders
By Richard Rost
2 years ago
Sum Last Orders by Customer in Microsoft Access In this Microsoft Access tutorial, you will learn how to generate the sum of the last orders for each of your customers. I will show you how to create aggregate queries to identify the most recent orders, use DSum to display totals, and provide tips for handling customers with multiple orders on the same date. Grant from Olathe, Kansas (a Platinum Member) asks: Most of my regular customers make a single purchase each month, usually for the same items. How can I generate a total that includes only each customer's most recent order? I'd like to use this information to estimate my sales for the upcoming month. I can't simply use last month's overall sales data because it includes many walk-in customers, but my regulars purchase almost consistently every month. MembersIn the extended cut, we will learn how to use VBA to achieve the same result we just accomplished with queries. I will show you how to loop through all the customers, determine their most recent order, and add the totals using a record set loop. 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, most recent order sum Microsoft Access, last orders by customer Access query, sum recent customer orders Access, aggregate queries Access tutorial, DSum for last orders Access, max order date query Access, calculating order totals Access, Access customer order totals, last order ID sum Access
Intro In this video, I will show you how to generate the sum of the most recent orders for each of your customers in Microsoft Access. We'll walk through setting up an order entry system, creating aggregate queries to find the latest order for each customer, and dealing with cases where customers have multiple orders on the same date. You'll learn how to use the DSum function to total these last orders and see how to organize and structure your queries for better management and reporting. This process is ideal for estimating sales based on your regular customers while excluding one-off purchases.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today we're going to talk about generating the sum of the last orders of each of your customers.
Why? Well, I'll explain why in just a second. Today's question comes from Grant in O'Lake, Kansas, one of my platinum members. Grant says, "Most of my regular customers make a single purchase each month, usually for the same items. How can I generate a total that includes only each customer's most recent order?" I'd like to use this information to estimate my sales for the upcoming month.
I can't simply use last month's overall sales data because it includes many walk-in customers, but my regulars purchased almost consistently every month.
I get what you're saying there because I do kind of the same thing. I've got lots of different memberships and I like to say, "Okay, of the members, because I got each customer tagged as a member, what were my sales last month for just the members?"
All right, give me each of their last orders, and you can use that to forecast what next month's gonna be. But it ignores all the random one-offs and you know, you never know when some guy's gonna randomly come in and spend a thousand dollars or, you know, something like that. So I get what you're trying to do. Let's see how we can do it.
First up, we got some prerequisites. This will be an expert-level video. What does that mean? Well, it's beyond the basics, but it's not quite programming. We don't have to use any VBA to do this, although for the extended cut in the members later, I'll show you a method where you can use some VBA to do this, in my opinion, which is actually easier.
But yeah, so what do we need? Well, this involves order entry and using orders, so go watch my invoicing video if you have not yet watched this. I show you how to build an order entry system and an invoice.
We're also going to use some aggregate queries, so go watch this video. Some queries, total queries. They've got lots of different names, but are technically called aggregate queries. And go watch my video on DSum. You don't need to watch this one, but I'm gonna show you how you can put the total right on the main menu with a single DSum once you build the query. You can then use DSum to display that value.
All right, we're gonna build this query back here. It's got each customer, each of their max order IDs, and then the order totals, and then the main menu will just use DSum to add all that up.
Okay, these are all free videos. They're on my website, they're on my YouTube channel, so watch those and then come on back.
Okay, here I am in the TechHelp free template. This is a free database you can download off my website if you want to. We've got customers, and each customer can have orders, and each order has order details, and those add up to an order total.
Now, what you want is you want the most recent order for each customer, and we're gonna add all those up. Just to go over some of the table structure, just for those of you who maybe aren't super familiar with the order entry system that I built, you got the customer table, you got the order table. Right, here's the order information. Notice there's no total in here though, because we get the total from the line items, right, quantity and price of each item.
Now, even in this table, there's no total. Why? Because we don't put totals in our tables. That's what queries are for, right? So the order detail table has an order detail query associated with it, and there's the extended price. That's simply the quantity times the unit price. That gives you the extended price in a calculated field in our query.
And for each order, you can see there's the items for order one, right? In fact, let me sort this, this way, sort, right, there's all the items for order one. These will all add up to the order total for that order, which is then, oh, save changes, yes, which is in the order summary query, and there it is right there. Okay, there's the order total.
Now, I'm gonna change a few of these around because I think I'm the only customer with two orders. So let's give a couple of different customers a couple of different orders.
I'm gonna go into the order table and play some games here. So you got one, two, one. Let's make this for me. Let's make this for customer two. We got 14, 17, 18. Let's make another one for me, and then let's do two more for customer two down here.
And notice that they're both on the same date. That's gonna be important because that's gonna be an issue later on. I will think we'll take that into consideration.
Okay, so if I sort these by customer ID, now you'll see that I've got four orders and customer two has four orders, two of which are on the same date. Okay, so what I want is the most recent one of these. Okay, and we're gonna add those up, and then of course everybody else has two.
Now, first thing I'm gonna say is if you want to put any kind of filtering in this, like for example, you mentioned it's just your regular customers, well, I assume you got some kind of field in your customer table. I've got an is_active field; you can use that or make your own field called is_regular_order or customer. Same thing with orders, you might want to filter them based on only ones that are paid.
Okay, just to keep things simple, I'm just gonna include all customers and all orders, but you can add criteria if you want to. That's easy to do.
All right, so the first thing I want is a list of all the customers and their max order date. What's the most recent order that each customer has placed? So we'll go to create query design. I'll bring in my customers and my orders. You can use the tables for this because we don't need any totals yet.
Okay, I'm gonna bring in the customer and their order date, and now I'm gonna change this to an aggregate query and set this guy to the max. Don't use last; I got a whole video on why you don't use first and last. Stay away from those. All right, I'll put a link to that one down below. Use max and min for dates. Min is the oldest, max is the most recent.
Okay, now when you run this guy, excuse me, when you run this guy, now you'll see the max order date for each customer. All right, customer one, my max order date is the 16th. All right, March of last year, whatever. All right, his, his, and so on.
All right, now we're gonna save this guy as customer_max_order_date_query. Now, one of the things that I see people do, especially beginners, is you try to do too much with one query. Don't try to add more to this. This is exactly what we need at this point—just a list of each customer and their max order date.
Okay, now close that query. We're good with it. Don't try to shoehorn too much stuff into one query. All right, so now we got a list of those orders, right, those order dates. Now I want the details from that, so let's make another query, and now we need to bring in the order ID and the order total from each of the orders that match that order date.
All right, you with me? All right, so another query, create query design. We're gonna bring in that query we just made, and now we're gonna bring in the order summary query because we want the order total and that's on that one.
Okay, I want to see down here... Well, let's bring in the customer ID. Let's bring in the order ID. We want the order date, and we want the order total. Now, how do we join this? This is where it gets tricky.
Obviously, we want to join it by customer, and I want the specific order that matches this max order date. If I want me, I only want one order, so I want max order date to line up with this order date. Yeah, you got two joins on one query there, see that? That's pretty freaky, huh?
All right, now if I run this...look at that. There's each customer. Let me sort it by customer. Let's go back here, sort by customer, and you'll see the problem when we do this. Watch. So, for customer one, matched up on that order date, it turns out to be that order ID, and there's the total.
Here's where the problem creeps in. Customer two has two orders on the same date, so that's not good. So we need to take a step back. I like to show it this way because this is the way my brain worked it out when I first sat down to do this, and I want to show you how I got to this point.
So we got to take a step back, and before we do this, we're gonna make another query that's gonna say, "Hey, of this list, I only want the max of the order ID as well." Then I can match it up with the order total. Okay, so let's take a step back.
So let's take order date and order total out of here. Okay, what I'm gonna do is I'm gonna say with a customer ID and the order ID, I want the largest order ID in this group, so if I run this here, you can see...there we go. So I want to also make this an aggregate query and give me the largest of those.
So that's gonna be...make this an aggregate query, and then this will be max as well. And now if I run it, that fixes the problem, see?
So don't try to do too much in one query, folks. Okay, now I got a list of each customer and the single order that is the most recent one. If you have orders where you are using now instead of the date for the order date, you probably won't run into this problem. I use now for my database because I want to know exactly what time of day stuff comes in.
Okay, all right, so let's save this one now. This is gonna be...let's do customer_max_order_date_one_order_query. Yeah, they get long. I like to make these descriptive so I can tell what they are later on. Okay, now we can use the results from this to make another query where we can now bring in those details that we need.
All right, so bring in that guy. This now has a list of each customer and their max order ID for the last order date. Could you have just done the max order ID in the first place? Yeah, kind of, maybe. See, the problem with auto numbers is that you can't always assume that auto numbers are gonna be in the right order chronologically.
All right, you might add an order, like, let's say it's order ID number six, and then later on you change its date. Maybe it was a quotation and they sat on it for a month and they changed it. So the order IDs aren't necessarily in the same chronological order. That's why we had to look at the date first, then give me the largest ID, and I'm just taking the largest ID because that's the only other way you can really figure out what the most recent order is. It might not be, but there's no other way to tell unless you store your dates to the second.
Okay, so now we got this. Now we can also bring in our order summary query. Okay, link by customer, and we have the max...actually, we have the order ID. We don't have to link these by customer. We just have to link them by the max order ID to order ID.
And if we show the customer, the order ID, the order date, and the order total, that should give us what we want. Yep, there it is. See? There's each customer, their specific order ID, the order date, and the order total. Now I've got the most recent order total for each customer's most recent order.
Okay, and now I can save this as my...this will be my final one. This will be the customer, let's call it last_order_amount_query. Okay, and now what we have to do is add these up to get the total that you're looking for, right? So remember it's customer_last_order_amount_query and order_total. So now we use a DSum. We'll put it right here in this field.
Right, design view. We'll call this last_order_total, and you know what it means, right? Open up the properties for this guy. Name will be last_order_total. The control source...I'll zoom in so you can see it...shift F2. This is going to be equals DSum(order_total, "customer_last_order_amount_query"). Do we need any criteria? Nope. Just add everything up in that query.
Hit OK, we'll change this to currency. Save it, close it, close it, open it, and there you go. There's your last order total, each of your customers' last orders, add them all up.
That's a lot of steps. I know, it's a lot of steps. It could be a little tricky sometimes working with these queries because, like I said, don't try to do too much in one query and just get what you want, get your results, and then, "Okay, we're good with that one. Now, what's the next step?" And sometimes it takes two or three queries to do it.
Can you do it without queries? Yes, you can. In fact, my brain initially went to a record set loop because you could loop through all of the customers and then inside that loop say, "Okay, for this customer, what's their max order date? What's that order total? Add it to a total variable," and then loop through all the customers that way. You hopefully should get the same result.
So we'll do this in the extended cut for the members. So members, sign up to get access to all my extended cut videos. Gold members can download these databases that I recorded the TechHelp videos in. You get all kinds of free classes and all kinds of other stuff. Check it out. Click the join button down below.
And also, while I've got your attention, if you like this kind of stuff, this queries and all these joins and DSum and all that kind of stuff, check out my Access Expert lessons. Gold members get a free expert lesson every month after they finish the beginner classes. So there's lots of benefits to becoming a member.
But I got 32 levels of expert lessons that don't involve any programming, that cover all the really cool stuff with Microsoft Access, including building a custom order entry system right over here. All right, we spent a lot of lessons on that one. But that's gonna do it for today, folks. There's your TechHelp video. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut.
A special thank you and shout-out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions. Manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout-out to Samir Shama from Shama Consultancy. Samir is a certified Microsoft Office Specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Samir is your guy. Check him out at shamaconsultancy.com.
TOPICS:
Generating the sum of the last orders for each customer
Using order entry system and invoices
Creating aggregate queries
Using DSum function in Microsoft Access
Building a query to find each customer's max order date
Including order details for each customer's last order
Handling duplicate orders on the same date
Filtering orders by customer status and payment status
Creating multiple aggregate queries to structure data
Combining aggregate queries to find order totals
Displaying calculated order total on the main menu
Saving and organizing queries for better management
COMMERCIAL:
In today's video, I will show you how to generate the sum of the last orders for each of your customers. This process begins with creating a list of each customer's most recent order. Then, we take it a step further by matching those orders with their order details. I guide you through setting up aggregate queries, explaining why using functions like DSum can be a game-changer. This tutorial is perfect for those who want to forecast sales without including random walk-in purchases. 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 goal of the video tutorial?
A. To show how to calculate total sales of all customers
B. To show how to generate the sum of the last orders of each customer
C. To explain how to calculate the average order value
D. To demonstrate how to build a new customer database
Q2. Why is it important to exclude walk-in customers when estimating monthly sales for regular customers?
A. Walk-in customers typically purchase different items
B. Walk-in customers have inconsistent purchasing patterns
C. Walk-in customers never return for future purchases
D. Walk-in customers often purchase more expensive items
Q3. According to the video, why is it recommended to use the MAX aggregate function instead of LAST?
A. The MAX function is faster
B. The LAST function is less accurate
C. The MAX function automatically filters the data
D. The LAST function is not supported in Access
Q4. What does the narrator suggest about storing totals in tables?
A. They should always be stored in tables for easy access
B. They can make tables difficult to manage
C. Totals should not be stored in tables; they should be calculated using queries
D. They should be stored in separate summary tables
Q5. What type of query is used to get the most recent order date for each customer?
A. Crosstab query
B. Append query
C. Aggregate query
D. Update query
Q6. What should you do if you want to filter results based on specific criteria like 'only paid orders' or 'regular customers'?
A. Add the criteria directly in the final query
B. Use a filter function in the programming code
C. Add criteria in each individual query
D. Apply the filter after exporting the results to Excel
Q7. How does the video describe handling multiple orders on the same date?
A. Using a random selection to pick one order
B. Selecting the order with the highest amount
C. Including an additional step to select the maximum order ID
D. Ignoring all orders if there are multiple on the same date
Q8. What is the preferred method to add up the last order totals from each customer suggested in the video?
A. Using a VBA script
B. Using the DSum function in Access
C. Manually adding the totals in Excel
D. Using a pivot table
Q9. Why does the narrator caution against doing too much in one query?
A. It can make the query difficult to read and debug
B. It can slow down the performance significantly
C. Access has a limit on the number of operations per query
D. It can lead to redundant data fields
Q10. What problem might arise when using auto numbers to determine the order sequence?
A. Auto numbers might not be unique
B. Auto numbers can be rearranged by the database
C. Changing order dates can lead to out-of-sequence auto numbers
D. Using auto numbers makes queries more complex to write
Q11. What is the purpose of using the Design View in Access as discussed in the video?
A. To create tables
B. To add conditions or formulas to the query fields
C. To visualize data entries
D. To print reports directly from the database
Q12. What series of lessons does the narrator recommend for understanding more complex queries and functionalities in Access?
A. Access Beginner Lessons
B. Access Programming Lessons
C. Access Expert Lessons
D. Access Reporting Lessons
Answers: 1-B; 2-B; 3-B; 4-C; 5-C; 6-A; 7-C; 8-B; 9-A; 10-C; 11-B; 12-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 Access Learning Zone addresses the concept of calculating the total of the last orders placed by each customer. I'm Richard Rost, your instructor, and today we're focusing on a query that can help you make future sales predictions by using the most recent order information of regular customers. Instead of relying on last month's total sales, which might include sporadic walk-ins, we're isolating data for those who consistently purchase each month.
One of our platinum members, Grant from O'Lake, Kansas, posed an interesting question: "How do I compile a total using only the most recent order from each customer?" This approach can impressively assist in estimating upcoming sales. I can certainly relate to Grant's needs, as I also analyze my members' activities similarly to forecast my own sales and filter out random transactions.
Let's break down the process involved. It's slightly advanced but doesn't require VBA, although I will share some VBA methods in the members' extended cut, which can sometimes simplify the task.
Firstly, ensure you're familiar with order entry and invoicing systems. I recommend checking my previous videos on building these systems if you're not already. This tutorial also requires an understanding of aggregate queries, so reviewing my DSum-related content is beneficial, too, as it shows how to display totals using DSum efficiently.
We'll construct a query listing each customer's maximum order ID and corresponding order totals. We'll then use DSum to calculate a complete total for the main menu. The completed task can be seen on my website and YouTube channel.
In our scenario with the TechHelp free template, every customer has associated orders that detail their purchases. Our goal is to sum all the most recent orders. Observing the table structure, you'll see that totals aren't stored directly in tables but determined through extended price calculations in order detail queries.
I'll adjust a few orders as examples. After manipulating the data to create scenarios where the date is a point of conflict, we'll proceed to build essential queries. It's crucial to adapt this to your own filtering preferences, like distinguishing between regular customers or specific payment statuses.
Our initial task is to extract a list of customers alongside their maximum order date. This involves creating a query, setting it as an aggregate query, and employing the max function to filter dates effectively. Using a descriptive name for this query will aid in maintaining clarity.
Next, we dive deeper by joining this result with the order summary to identify the corresponding order totals. Sometimes, having multiple identical dates for different orders might complicate things; hence we refine our query further, focusing on maximum order IDs per customer. Secure these detailed results with a second query, which helps resolve discrepancies without overwhelming one single query.
Finally, let's incorporate DSum to aggregate our query results for a synthesized total, showcasing each customer's latest purchase total. This sum can be effortlessly displayed on your main menu. Decomposing tasks into incremental steps is key; resist the urge to overcomplicate individual queries.
An alternative approach involves using a record set loop, looping through customers to obtain and accumulate their maximum order totals. This will be further explained in the extended cut available to members.
To explore additional examples of queries, joins, and functions, consider my Access Expert lessons. The program spans multiple levels and offers members enriching content that bridges non-programming practices with advanced Access functionalities.
In summary, this method aids in understanding and implementing a practical query system for better sales forecasting. For a detailed video guide on all the topics mentioned, visit my website through the link below. Live long and prosper, my friends.Topic List Generating the sum of the last orders for each customer
Using order entry system and invoices
Creating aggregate queries
Using DSum function in Microsoft Access
Building a query to find each customer's max order date
Including order details for each customer's last order
Handling duplicate orders on the same date
Filtering orders by customer status and payment status
Creating multiple aggregate queries to structure data
Combining aggregate queries to find order totals
Displaying calculated order total on the main menu
Saving and organizing queries for better management |
