Second Last Date
By Richard Rost
2 years ago
Second Last Order Date in Microsoft Access In this Microsoft Access tutorial, I will show you how to determine the date of the second last order placed by a customer, using domain aggregate functions like DMax. We will explore methods to accurately extract and compare order dates, even when multiple orders are placed on the same day. Simon from Blue Springs, Missouri (a Gold Member) asks: This might seem like a weird question, but I need to figure out how to find the second last date that a customer placed an order. I know I can use DMax to determine the most recent order date, but how can I find the order before that? Why I need to know it? Don't ask. Long story. MembersThere is no extended cut, but here is the database 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, second last order date, second last customer order, DMAX function tutorial, order date retrieval, customer order history, DMAX example, finding previous order dates, Access query tutorial, Access expert tutorial, domain aggregate functions, advanced Access functions, customer order dates, lookup order dates, Access date functions
Intro In this video, we talk about how to find the second last order date for a customer in Microsoft Access. Using functions like DMAX and DLOOKUP, I'll show you step-by-step how to set up form fields to display both the most recent and the previous order date, and explain two different methods for handling cases where customers have multiple orders on the same day. We'll discuss criteria filtering, string concatenation, and how to work with auto numbers and order IDs to make sure you find the right order dates for each customer.Transcript Today we're going to talk about how to find the second last date. What does that mean? Well, you know how to find the last date, like the last order date, the last date that a customer placed an order. How do you find the date before that one, the second last date? That's what we're going to talk about today.
Today's question comes from Simon in Blue Springs, Missouri, one of my Gold members. Simon says, this might seem like a weird question. Simon, you have no idea the weird questions that I get. This is nowhere near a weird question. But I need to figure out how to find the second last date that a customer placed an order. I know I can use DMAX to determine the most recent order date, but how can I find the order before that? Why I need to know it? Don't ask, long story. No, I get it, I get it. I've had clients before that have wanted to find all kinds of weird stuff. So this is by no means a weird request. Basically, we can do this with DMX with the proper criteria.
We'll talk about that in just a second. For the rest of you who don't know how to use DMX, this will be an expert-level video which means it's a little bit beyond the basics. It's not quite a beginner topic, but you don't need any programming for this. Just the use of a couple of functions. What functions, you ask? Well, there's DMAX. If you've never used DMAX before, go watch this video. It's for finding the maximum value in a table or query. And DMAX is a close cousin of DLOOKUP, which DLOOKUP is the first and most popular of all of the domain lookup functions. That's what that D stands for. And a domain is simply a set of records, a table, a query, whatever.
So if you're not familiar with either of these, watch DLOOKUP first, learn how to use DLOOKUP, and then use DMAX. There's a bunch of them. There's DCOUNT, there's DSUM, there's a whole bunch of D stuff. But go watch these. If you haven't watched them before, they're free. They're on my YouTube channel. They're on my website. Go watch those and come on back.
All right, so here I am in my TechHelp free template. This is a free database you can download from my website if you want to, and in here we have customers, and customers have orders. Okay now, these orders aren't in any particular order. I get it. All right, we've got one from March of 2023, another one from March 15th. Let's add another one from today. I'm gonna come in here and go control semicolon that puts today's date in there and another order and he ordered some stuff. Okay, good enough. Now I'm gonna sort these in reverse order. I'm gonna right-click and go newest to oldest so we can see, okay, the most recent orders today, July 12, 2024. The one before that goes back to March of 2023, that's the one I'm looking for.
Okay now, you can do these calculations anywhere you want. You can put them in a query, you can put them in a form. I'm just going to make little form fields on here. So when we open up the customer's record, it will immediately show us what we're looking for. Okay, I'll just grab one of these other fields like credit limit, copy, paste, and let's slide it down maybe over here. Okay, and let's put this guy, let's call this the max order date. Max order date. Okay, we'll change this over here. We'll say max order date and get rid of the control source. We're no longer binding this to credit limit. Get rid of that format. It's no longer a currency and for the control source, this is where our DMAX function goes.
I'm going to zoom in so you can see this better. Shift F2. That'll zoom in and this one's pretty straightforward. It's gonna be DMAX. What do I want? I want the largest of what field? Order date, right, from the order table. What's my criteria? Where the customer ID equals the customer ID on the current form. This is a field on the form. It's right there, customer ID. OK. And we're going to concatenate that onto this. So this whole string is going to end up reading customer ID equals one or six or whatever number we're on. Don't put this inside the string. Do it like I have it right here. You'll save yourself some headache. Yeah, it sometimes works the other way but not always. Hit okay, save it, close it, close it, open it and there we go. There's the max order date. If you look in here, you'll see that's the max order date. Perfect, now we want the one before that.
Now, there's two ways you can do this, and it all depends on what you're looking for. If I say, show me the next oldest order date, then that won't take into consideration if they have multiple orders on the same day. If they came in here, or if they placed three orders on this date, and I look for a date that's older than this one, then you might get, you know, four orders ago because they placed three orders today, but that might be what you want. Right if they came into the store today and placed three separate, now, they went to the counter they ordered something, they all, I forgot, so I went back and got a shovel. Alright, now that's a second order on the same date. If that's what you're looking for, that's fine. If you want to know the previous date that they placed an order, then that's fine. That'll be method number one that I'm going to show you. It'll ignore duplicate orders, okay? But you might want to know if they placed another order, and even though the second order was placed on the same day, you might want to know that too.
So there's two different ways you can approach this problem. It all depends on what you're looking for. I'm gonna show you both methods. One's a little easier than the other. So let's go over here at the design view, copy this bad boy, copy, paste, we'll see control. This will be the previous order date and let's come in here.
Let's change your name to previous order date and let's take a look at our control source now. All right. Now, I'm looking for the date before the date that I just found. So before the max order date. So I'm still going to use DMAX. I still want to find the largest value but I want the second largest value. So I want the order date from order T where this customer is the same but I got to add a criteria onto here so ampersand remember this is a string right so I'm gonna put quote space don't forget that space that space is important and now I want the date where the order date is less than the one that I just found so I'm gonna say where the order date is less than now dates have to go inside of hashtags or pound symbols or octothorpes I'm gonna call this guy right and max order date and close that thing up okay so let's say max order date was July 12th today right this will get replaced with July 12th 2024 and then my criteria will be customer ID equals one and order date is less than July 21st, whatever. Okay, see how that all works. Let me make this a little bit easier to read here. There, there you go, that's easier. There's the customer ID and order date, blah, blah, blah, blah, blah. Okay, I know all this concatenation stuff is tricky, but this is just, you just gotta, you gotta learn it folks. I got several videos on concatenation. I'll put links to them down below if this stuff is tricky for you at all. Alright hit OK. Alright save it, close it, close it, open it and there we go. There's the order date before that one. If we look in here you can see there and there we go.
Okay now once again if the user has multiple orders on the same date this won't give you the second last order. It gives you the second last order date which is correct, but if I come in here and put another order in the system for today right blah blah blah blah blah blah blah blah okay close this close this reopen it and it's still giving you 3/15 even though the previous order is still 7/12.
So if you want to find this we have to use a slightly different strategy. What we have to do in this case is say okay. I know the order date of the max order date. Give me its ID. So we'll use DLOOKUP now. I want you to find the order that precedes this one. That's not this guy but is less than or equal to that date. Okay or you could literally say give me the max of their order dates except for that one. All right so let's go back here let's get the order ID for this one. It won't matter which one you pick right there's there's two orders in here there's 18 and there's 17 unless you really care you know you you could say give me the largest ID if you want to, but again, you can't always assume that IDs are in order. But unless you put time stamps on here to where you know the order date by the second, then I mean, you could grab the order ID, you know, use the max of that if you want to. That's up to you. Again, these are your Legos, however you want to put it together. So I'd say let's use the max of the auto number. There's really no other way around it.
Okay, so design view. This is why personally when I do an order entry system, I like to store the time in there as well. Because even though you might not think upfront that you need that information, later on down the line you might. It might make a difference to you which one was placed first to the minute or to the second even. Okay?
All right, so let's copy this, copy, paste, I'm going to slide this one over here. This is going to be the max order ID. We're going to get the max order ID. So open this guy up. This will be max order ID. Now, we already know the max order date. So we're going to de-max the order ID from the order table for this customer where the order date equals that max order date. In other words, give me the largest auto number for this customer, for the customer's orders from this date. So if there are three orders on the same date I want the most recent one based on the auto number. Okay, save that, close it, close it, open it, and, oh, got an error. Error, what'd I do?
Let's take a look. Oh, I see it. See, sometimes when I'm talking to you guys, my brain is running, my brain and my mouth are running faster than my hands and my keyboard. Anybody see the error? See where it is? Read it to yourself, customer ID equals one. That right there. Forgot my "and", see, sometimes it's easier if you can see it like that. And order date equals whatever, forgot my "and". And I leave these mistakes in the video because if I make the mistake, chances are you'll make the mistake, so there we go. All right, max order ID is 18. So, there are two orders on the same date. That is the max one. So now to find the second one, to find the second order, you could look up the ID or you could look up the date, whichever one you want. I'll just look up the date.
All right, so let's find the second order date. So copy, paste, slide this over here. I'll call this here, I don't like starting my names with a number. You can, but I don't like to, so I'm going to call this second order date. Alright, so we're going to look up the order date where, let's get rid of this, we don't need to worry about this anymore, we're going to de-max the order date where the order ID that we just found and it's a number so we don't have to enclose it inside of anything.
Hit OK. Max order ID, just double-checking, yep. Close it, close it, open it, and there we go. So the second order date is also on July 12th because there's two of them. So we just found that one. We found the order, the max order where it's not order 18. All right, you with me? So again, it's all a matter of which method you want to use to look up the second order. You want the second order date, the date on which they were previously ordering, or do you want the actual order from the second order date? You get it. Yeah, that is a weird question.
No, I'm just kidding. I'm just kidding. It's not really that weird of a question. I see lots and lots of stuff like this. That's why I do the TechHelp videos. If you like learning this stuff and you want more, I got lots of lessons on my website, folks. Access Expert Level 27 is where I start my complete guide to access date and time functions. We go through all kinds of stuff like that. I go through all the different functions in my Access Expert series. In Access Expert Level 29, we start working on the domain aggregate functions. The lookup, the sum, the count, the average, the max, the min, the first, the last. I can make a poem out of this stuff. All kinds of functions, all kinds of examples. This lesson alone by itself is over two hours. So we cover this stuff exhaustively folks, exhaustively.
Like me right now, I'm exhausted. I'm sick for two weeks. So check all that out. It's on my website. I'll put links to it down below in the description. You can click on it. If you have any questions, post them and that's gonna do it. That's gonna be your TechHelp video for today.
I hope you learned something, my friends. Live long and prosper. I'll see you next time.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
TOPICS:
How to find the second last date
Using DMAX to find the most recent order date
Creating a form field for the max order date
Filtering DMAX with customer criteria
Finding the second largest order date
Handling multiple orders on the same date
Using DLOOKUP for order IDs
Concatenating strings in criteria
Dealing with auto numbers and order IDs
Storing time in the order entry system
Strategies for finding the second order date
COMMERCIAL:
In today's video from Access Learning Zone, we tackle a tricky problem: finding the second last date a customer placed an order. Simon from Blue Springs, Missouri asks how to go beyond just the last order date using DMAX. I'll walk you through two methods depending on your needs, even when there are multiple orders on the same day. We'll use key functions like DMAX and DLOOKUP in practical ways. By the end, you'll know exactly how to pinpoint that elusive second last order date. 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 purpose of the video tutorial?
A. To explain how to find the maximum order date
B. To show how to calculate the average order value
C. To teach how to find the second last date an order was placed
D. To demonstrate how to create a date range filter
Q2. What function does Simon mention he uses to determine the most recent order date?
A. DLAST
B. DSUM
C. DMAX
D. DLOOKUP
Q3. What is DMAX used for in Microsoft Access?
A. To count the number of records in a domain
B. To look up the greatest value in a domain
C. To sum all values in a domain
D. To find the average value in a domain
Q4. In the example given, how are the orders sorted to find the second last date?
A. Oldest to newest
B. Alphabetical order
C. By customer ID
D. Newest to oldest
Q5. Why might you choose to use timestamps in an order entry system?
A. To track the time employees log in
B. To record the specific time an order was placed, which can be useful for resolving order sequences
C. To include additional information in reports
D. To ensure all data is backed up
Q6. Which function is described as the most popular of all the domain lookup functions in the video?
A. DMIN
B. DSUM
C. DMAX
D. DLOOKUP
Q7. What might be a reason for using DLOOKUP in conjunction with DMAX?
A. To find the total sum of orders
B. To locate the record with the smallest value
C. To identify the next most recent date after the maximum order date
D. To merge two tables
Q8. What common error is pointed out when constructing criteria for the DMAX function?
A. Forgetting to include the date format in quotes
B. Omitting the 'AND' when adding multiple criteria
C. Using an incorrect table name
D. Missing a semicolon at the end of the criteria
Q9. Which approach is suggested to handle multiple orders placed on the same day?
A. Only record the first order for each day
B. Use the maximum order ID to determine the most recent order on that date
C. Ignore all orders placed on the same day
D. Summarize all orders into a single record
Q10. Where can additional lessons on Microsoft Access date and time functions be found according to the video?
A. On a competitor's website
B. On the instructor's YouTube channel
C. In local libraries
D. In Microsoft Office documentation
Q11. What is the benefit of watching the other D- functions tutorials mentioned in the video?
A. They provide access to discounts on software
B. They cover basic features of Microsoft Office
C. They offer in-depth explanations and practical examples, enhancing the understanding of domain aggregate functions
D. They provide entertainment content
Answers: 1-C; 2-C; 3-B; 4-D; 5-B; 6-D; 7-C; 8-B; 9-B; 10-B; 11-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 is all about finding the second last date, specifically when you want to determine the date before the most recent order placed by a customer. This is a common question if you've ever needed to track order history beyond simply knowing the latest activity.
Most people are familiar with how to use DMAX to find the latest, or maximum, order date for a customer. However, the challenge comes in identifying the order date immediately before that, the so-called "second last date." There are a few different ways to solve this problem using Microsoft Access functions. While this topic is a bit more advanced than the basics, as long as you have some familiarity with functions like DMAX and DLOOKUP, you will be able to follow along. No programming is required - just an understanding of how to leverage these aggregate functions.
If DMAX or DLOOKUP are new to you, I recommend learning the basics of DLOOKUP first, since it lays the groundwork for understanding all the domain aggregate functions in Access. Domain refers to a set of records, such as a table or query, and these functions allow you to search, count, sum, or find the maximum value within that domain. DMAX, for example, is commonly used to retrieve the largest or most recent value in a field. Both DMAX and DLOOKUP, along with functions like DCOUNT and DSUM, are covered extensively in other lessons, so be sure to check those out if you need a refresher.
Let us take a closer look using my TechHelp free template database, which you can download from my website. In this database, we have customers and each customer has orders attached to their record. These orders are not sorted by date by default, but for the purpose of demonstration, you could sort them from newest to oldest to see the progression. Say, for example, you add a new order today. Sorting the orders, you could clearly see which is the most recent, or max, order date, and which is the previous one.
You can place the calculation for the max order date wherever you prefer, such as in a query or directly on a form. In my example, I walk through how to set up a form field that automatically displays this information every time you open a customer record. The basic setup involves duplicating an existing field for convenience, renaming it (for instance to "Max Order Date"), and entering the formula that uses DMAX. The function will look for the highest order date in the orders table for the current customer by matching customer IDs. It is important to build this string criteria carefully: include the customer ID from the form rather than placing it directly in the function string to avoid troubleshooting headaches later.
Once you have the max order date working, it's time to tackle the real question: how do you find the date before that? This depends on what exactly you need. If you're simply looking for the next oldest order date, you can use DMAX again with additional criteria. This approach, however, does not account for customers who may have placed multiple orders on the same day. In such cases, looking for an order date less than the max order date will skip all other orders that happened on that top date, which might be what you want. But if you need the true "second-to-last" order, even if it happened on the same day as the latest, you'll need a different method.
Here are the two approaches in summary:
1. If you only care about the previous order date, regardless of how many orders were placed on a single day, you use DMAX to find the largest order date that is still less than the max order date for that customer. This method ignores duplicates on the same date.
2. If it's important to include each separate order, even multiple orders on the same day, you need to work with order IDs in addition to dates. First, use DMAX to find the max order ID for the max order date and customer. If there are multiple orders for that date, you'll get the highest order ID. From there, to find the true second-to-last order, you can look up the preceding order ID or order date, as appropriate.
Throughout the process, careful string concatenation in your criteria is vital. Remember that dates in Access calculations should be surrounded by the pound (hashtag) symbol for proper recognition. If the logic or formula building feels overwhelming, I have several tutorials specifically addressing string concatenation in Access, which can provide more clarity.
When considering duplicate orders on the same date, it often makes sense to store the order time as well as the date. This gives you the ability to identify the specific order sequence, right down to the minute or second if necessary. Without this extra information, using auto numbers (order IDs) is often your best option for pinpointing order sequence, particularly when dates alone are insufficient.
To recap: you have options depending on whether you care about duplicate order dates or not. You can find the second largest order date for a customer with DMAX and a carefully constructed criteria string, or you can include order IDs and use DLOOKUP as needed for more specificity. These methods allow you to adapt the solution based on your database structure and business needs.
If you want to become an expert in Access date and time functions or domain aggregate operations, I recommend checking out my Access Expert Level 27 for a comprehensive look at all things date related, and Access Expert Level 29 for deep coverage of domain aggregate functions like lookup, sum, count, average, max, and min. There are plenty of step-by-step practical examples in those lessons.
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 Finding the second last order date for a customer
Using DMAX to get the most recent order date
Adding a form field to display max order date
Setting up DMAX criteria with customer ID
Calculating the previous order date using DMAX
Handling cases with multiple orders on the same date
Differences between second order date and second order
Using DLOOKUP to retrieve the max order ID
Applying string concatenation in criteria expressions
Using auto numbers to identify most recent orders
Finding second order date with DMAX and DLOOKUP
Importance of storing time in order entry systems |
