|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
DMin By Richard Rost 
5 years ago
Find First Order a Customer Placed (Earliest Date) In this video, I will show you how to use the Dmin function to find the date of the first order that a customer placed. You'll see how to perform a Dmin operation with two parameters using the AND condition. You'll learn how to look up their discount amount from a table. We'll also see how to calculate the first order date for everyone using an Aggregate Query and the Min function. Eugene from Long Beach, California (a Platinum Member) asks: I run a membership program at my business. We base the amount of the member's discount on the length of time since they placed their first order. Over 1 year is 5%. Over 3 years is 7%. Over 5 years is 10%. Is there an easy way to calculate that and display it on their customer form? MembersMembers will learn how to take this one step further and determine the date of the second order the user placed. It's trickier than you think.
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!
Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, minimum value, min, dmin, dmin function, earliest date, earliest order, first order date, years as customer, discount lookup, 2nd order, second order
IntroIn this video, I will show you how to use the DMin function in Microsoft Access to find each customer's earliest paid order date and use that information to calculate their membership duration and assign a discount based on how long they have been a member. We will cover the prerequisites, demonstrate how to create an aggregate query to find the minimum order date, display that value on the customer form, handle nulls using the NZ function, and set up a table to manage and calculate membership discounts based on time as a customer.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.In today's video, I'm going to show you how to find the customer's earliest order date so you can calculate their membership discount, which is based on the total amount of time they've been a member, using DMin, the DMin function in Microsoft Access. Today's question comes from Eugene in Long Beach, California, one of my platinum members. Eugene says, I want a membership program at my business and we base the amount of the members' discount on the length of time since they placed the first order. Over one year is 5, 3 years, 7, over 5 years is 10. Is there an easy way to calculate that and display it on their customer form? Is there an easy way? No, not really, but there is a way. No, it will not be scary. I'm only kidding. Let me show you how to do it. We're going to use the DMin function. Now first, some prerequisites. First, I want you to know how to do aggregate queries because we can calculate everybody's minimum value, which is the earliest date. We can do that first with an aggregate query. I'll show you how to do that. So if you haven't watched my aggregate queries video, go watch that now. Then I want you to learn DLookup first. If you don't know DMin at all, watch DLookup. DLookup is the easiest one of the D family of functions to learn. So go learn DLookup. Go watch that. These are all free videos. They're on my website at those links. They're on my YouTube channel. Then after you learn DLookup, learn DMax. It's kind of the opposite of DMin. Max finds the biggest one. Min finds the smallest one. When it comes to dates, max is in the future. Min is in the past. If you want to find their first order, we've got to look for the smallest order date. Even if you know DMax and you watch that video, I'm still going to show you some extra new tricks in this video with DMin. For example, I'm going to show you how to use multiple criteria to look up the order value. Then, of course, watch my NZ function. If you don't know what the NZ function is, it takes a null value and converts it to zero or some other value for you. We'll use that today too. So go watch all four of these. If you don't know this stuff, go watch that now. Pause the video. I'll wait for you. Then come back. Here I am in my TechHelp free template. This is a free download up on my website. If you want to grab a copy, I'll put a link down below the video in the description. Click on the read more and you'll see all the stuff down there. There's a link section where you'll find links to all those other videos and my TechHelp free template. So go grab a copy of this. I've got a customer form, which has basic customer information, and each customer can have orders. I show how to build all of this in my other free videos. If you're not familiar with this data, basically watch that stuff. Here's the customer table. Simplistic customers. And I've got an order table with a list of orders. Now all we care about is the order date. We're going to make sure that all these are marked paid because you want to check and make sure that your orders are paid before you include them in the order stuff. I didn't have any of these marked paid, but we want to check that too. Now for each customer, I want to find out what the earliest order date is. We're going to use DMin. Let's make a query first. This is the easy way to do it. We'll use the min function in the query and an aggregate query. So create query design. I'm going to bring in the customer table and the order table. They'll be joined together by customer ID. Let's bring in the customer ID. Let's bring in the order date, and we want to make sure it's paid. So bring in is paid. We'll set the criteria down here to true. That way we're seeing only orders that are paid. If I run this now, there's all the orders and whether they're paid. But I don't want to see all of the orders. I want to see the smallest value here for each customer. So we're going to group them together by customer ID and display the minimum order date. We can hide is paid; we don't need to see that. I don't care to see that in the final. How do we do this? Let's turn this into an aggregate query here with the totals button. I'll move is paid up front. Order date is not going to be grouped by because that will show each unique group together. I'm going to drop this down and pick min. Pick min. Now when I run the query, there you go. There's the min of order date. If you don't want it to say min of order date, you can change it with an alias right here. Just come in front of order date and type in first order date or whatever you want to call it. Then run it. There you go. You've got customer ready and their first order date. Just double check to make sure that's correct. Customer 1 has got two orders on 1/14 and 3/14. If I change that, let's go back out here. You have to rerun the query, obviously. If I change this to, let's say, 3/14/2020, now when I run the query, there you go. 3/14/2020 is now his first order date. That's how you do this in a query. I'll save this query. We'll call this my customer order earliest q. So that's in there. If you want to display the customer's first order date on the order form, this is what Eugene wants to do. He just wants to have it down here in the bottom. Their first order date and what their discount is. You could now DLookup up the value out of this table. However, that's inefficient because this whole query has to run first. Then the DLookup will look up the specific customer. I don't want to calculate everybody's first order date. I just want to calculate customer 1's order date. So we're going to make a box on this form to do some stuff in here. To look up a value from a table of query, we use DLookup. To find the smallest value, which in this case is the order date, we're going to use DMin. DMax finds the largest; DMin finds the smallest. There's also DSUM. I have another video on DSUM where you can add up a bunch of stuff if you want to get this customer's complete order total, how much they have spent in their entire history. We want their first order date. We're not going to look it up in this query because we don't want that whole query to run. Let's go to design view. Eugene just wants it when he opens up his customer form. He can see this information down here. I'm going to copy the customer since field because it's already formatted as a date. Let's slide that down here. We're going to call this the first order. This is not a Star Wars reference. Let's rename this field. Double click on it to bring up its properties. Let me resize this a little bit. We're going to give this guy a name, which is going to be first order, and get rid of the control source. That's the customer since. It's no longer bound to a field in the table. Now it says unbound. That means we're going to put our own value in here. But we're not saving it to the table because this data is going to change every time you open up this customer; it's going to show you a new calculated value. What's going to go in here, the control source? It's going to be our DMin function. I'm going to hit Shift F2 to zoom in for you so you can see it more clearly. It's going to be equals DMin. We're looking at the order date, the smallest order date from the order table. Now we need two criteria here. First, it's got to be for this customer. So customer ID equals, close your quotes, and customer ID. That says go get the customer ID off of the current form, and that's your first criteria. We've done this before with DLookup and DMax. Now we're going to add a second criteria because I also want to make sure the order is paid. I don't just want to see the oldest quotation. I want their oldest paid order. So ampersand, we're going to add more on here. Then inside quotes, and the word and, is paid equals true. Close your quotes. Close your parentheses. Hit OK. There it is. I'm going to close that, save changes, yes, and let's open up the customer form. Look at that. 3/14/2020. Go to the next customer. The DMin runs again. The DMin runs again. New novel coming this fall. Next customer. Null values are fine in here; you'll just see a blank thing. You won't see an error there. The error comes if you have a null part of your criteria. That will throw an error message in here. But right now, it's OK because it's looking up a value that just doesn't exist. But if you had in here like customer ID equals and customer ID is null, that might actually be a problem if you come out here. Let's see. Go to customer form. Go to a blank record. There's your error. If you want to get rid of that, just come in here and put NZ around the customer ID. Like this: NZ, comma zero. Because you can't say customer ID is and then nothing - that will generate the error. But at least the NZ function will change that customer ID. If it doesn't exist into a zero, then it will try to look up with a DMin customer ID equals zero, which doesn't exist. It's OK if the value doesn't exist, but you can't send a null value as a parameter. There's the problem here. Now if I come in here and go to a blank new record, oh, at least I'll just see a blank record again. You don't want your end user seeing that pound error and wondering what that is. Now that I know when the first order was placed, I can calculate how many years this person has been a member, based on their first order date. Yes, I know I've got a customer since field here, but not everybody has that. If you already have several thousand records in your database, you might not want to have a customer since field. Or maybe they've been a customer, but their discount only kicks in as of the time they placed their first order, and these two dates can be different. There are a million reasons why you'd want to do this. This is just the example that I picked. We interrupt this broadcast for a quick commercial. If you're enjoying this lesson, I have lots more just like it. Check out my expert lessons. I cover all these different kinds of functions, DLookup, DMax, you name it. There's tons of stuff. Go check out my website 599CD.com/access-expert. Now, there is a function called DateDiff where you can say how many years difference between this and that, or how many months, or how many days. But you can't use DateDiff to calculate an age, because DateDiff works with whole years. So any date in 2020 and any date in 2021 will show up as one whole year. So even if it's December of 2020 and January of 2021, they'll still show up as having been a member for a year. So you have to calculate age differently. I've got a whole video on how to calculate age. I'll put a link down below in the links section. Basically, there are a couple of different ways to calculate age. There's an easy, simple way, and then there's a lot more complicated way. The more complicated way gives you a much more exact age, but for all intents and purposes for what we're doing here, this is just fine. It works. I think I figured it out 99.9 percent of the time. Just like leap years, those sometimes will throw you off. We're going to calculate the number of years that this person has been a member based on their first order. I'm going to calculate family size. That's already formatted as a number. Let's drop this down here. Let's call this years as a customer. We'll call it that. I'm going to get rid of that colon right there. Family size here, we're going to get rid of that. We're going to call this years as customer. Get rid of the control source. Again, I'm going to zoom in, Shift F2. Now, this is going to be - here's how you calculate age. You take date 1 minus date 2, divide it by 365.2425. It gives you a close enough age. Unless you're doing super legal stuff, this is good enough. So we're going to say today's date minus the first order date that we just calculated, first order. Divide that, which gives you the number of days between those two. In Access, the date value gives you a number of days, so 1 equals one day, 12 hours is 0.5. So I have a number of days there. If I divide that by 365.2425, that will give me the age in years, which is close enough. If I save that, close it, reopen it, there you go, there's your value. Now, I don't care about the fractional part. I just want to make sure that this first part here is at least 1. So I'm going to chop off that fractional part. I'll do that with the Int function. We're going to take the Int of all that, just chop off any fractional part. If it's 1.0001, it comes up to 1. If it's 0.999, it comes off to 0. That's how Int works. Save it, close it, open it back up again. There you go. Next guy, 0 years. It's currently September of 2021. So that's 0 years. Next customer. Let's put an order in for this customer here. I'll say the order date is 10/1/1999, and some stuff, some stuff, mark it paid, close it. You have to refresh this form, so F5, and it will reload those values. There it is: 21 years. That's perfect. Now, in order to calculate the actual order discount percentage, there are a couple of ways we could do it. We could do the easy way and use an If function, Immediate If, and do a little nested if, because there are only three options. But what if you want to add a fourth option in the future? Or what if you want to include this information in multiple places inside your database? It's always bad to hard code stuff like that. I'm going to actually make a table to store my discount stuff in it. Let's go over here, create table design. We're going to have a discount ID; that'll be an autonumber. We're really not going to use the pks there. Discount years - we're going to make that a number: 1, 3, 5, whatever else you want to put in there. Then the discount amount - that'll be a number. That's going to be a double format type percent. Save this as my discount T, my discount table. Let's go with what Eugene had for discounts. So discount years, after one year you get - what is it - 5. After three years you get 7. After five years you get 10. I'm just going to throw one more in there: I'll say after 10 years, you get 15. I threw my own in there. I get that too, Eugene. I don't know what you do, but whatever it is, I get 15 percent off. Let's save that. Now I've got a little table. See how easy it's going to be now in the future if you want to change this stuff? Make it 10.5, change the number of years, add more. Back to the customer form. Now we can put a little function down here to calculate that. Now, what are we going to do to figure that out? We actually have to use DMax for this. DMax. If you look at the table, I want the maximum discount amount from the discount T, where the years is less than the number of years the person has been a member. So if I've been a member for seven years, what's the largest discount amount that's under that? It's five. The number of discount years is five. See how that works? Here's how it's going to look in code. Design view. My discount, which is - copy this one, copy and paste, slide you up there - your discount. We'll call this the discount field. Shift F2. Get rid of this stuff. What we're going to put in here is equals DMax. So I want you to learn DMax first too. Look for the maximum discount amount. This assumes, by the way, that the discounts only go up. If you've got a table where they go up and down, they fluctuate, you'll have to do something a little bit different. This assumes that the longer you're a customer, the higher your discount gets. That's what I'm assuming. From discount T, what's my criteria? Where the discount years is less than or equal to years as customer. If you've been a customer for six years, what's the largest discount amount where the discount years is less than six? That'll be whatever the value is for five. Hit OK. Save it. Close it. Open it up. There we go. We can format that as a percentage: format, percent. Save it, close it, and now for each customer: one year, 5 percent discount; zero years, no discount. You can put that in the table too if you want to come in here. Actually, let me actually make this a value by just typing in a zero. Now you've got zero in your table, and so you should get a value there. Let's see. Yep, zero, zero percent. Then, 21 years, 15 percent - there's my discount. We're getting errors here. Got to make sure you have your NZs taken care of. Let's take a look at the formula again. Right here, open this up. We need to throw an NZ around years as customer, just like that. That way, it will send a zero to discount years. You've got to watch your criteria. One more try, here we go, and looks good. Still getting the error. Hang on, let's take a peek. What's going on here? That's my bet. I forgot my alternative value. So NZ years as a customer, comma zero. Put a zero in there. That's my fault. Now, let's try running it. I will leave my mistakes in my videos for you. I usually do. Sometimes, if it's a stupid mistake - well, I like leaving the stupid mistakes in the videos because if I make the mistake, then you're probably going to make the same mistake at some point. So by watching me make these mistakes, you're learning. But sometimes I get talking, as I do - for those of you who've been with me for a while and you've been watching a bunch of my videos, you know that I sometimes start running off at the mouth. I'll be doing stuff while I'm talking, and my brain isn't engaged with my mouth and my hand, and so I can put a dumb error on the screen. I don't want you to see that because that's embarrassing. But most of my important errors, like yesterday's TechHelp, I left a very important error in the video, because I made it. So you're probably going to make it. If you like this stuff and want to learn more, in my extended cut for members, 12 minutes long, I'll show you how to determine the customer's second order date. Why? I don't know. It came up in one of the forums. Someone wanted to know how to do it, but I could see it being useful. If you want to say that your discount is dependent upon you placing a second order within 30 days. I don't know. Businesses come up with all kinds of silly rules. Why would you want to do it? I don't know. But I'm going to show you how in the extended cut for members. Silver members and up get access to all the extended cut videos, and we're over 200 now, so there's lots of stuff to watch. How do you become a member? Click the join button below the video. After you click the join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the show more link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1. It's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the main purpose of using the DMin function in this tutorial?A. To find the customer's latest order date B. To find the customer's earliest order date C. To sum all the customer's orders D. To count the number of orders for a customer Q2. Before working with DMin, which function does the instructor recommend learning first? A. DSum B. DLookup C. DAvg D. Choose Q3. Why is it important to filter by "is paid" when searching for the earliest order date? A. To only include paid orders in the discount calculation B. To exclude orders with future dates C. To find the maximum payment amount D. To list all orders for every customer Q4. Why might using a pre-built aggregate query for pulling the first order date into every customer form be inefficient? A. The query must recalculate all customers every time it runs B. Aggregate queries cannot filter by paid orders C. Queries cannot join two tables D. It is impossible to display any aggregate data in forms Q5. What is the main difference between DMin and DMax? A. DMin finds the largest value, DMax finds the smallest B. DMin finds the earliest date, DMax finds the latest date C. DMin is only for numbers, DMax is only for text D. DMin and DMax are interchangeable Q6. What is the purpose of the NZ function in this context? A. Converts numbers to text for display B. Converts null values to zero or another value to avoid errors C. Formats dates as text D. Renames table fields automatically Q7. Which of the following formulas best calculates the number of years a person has been a customer based on their first order date? A. (FirstOrderDate - Today's Date) * 365.2425 B. (Today's Date - FirstOrderDate) / 365.2425 C. Int((Today's Date - FirstOrderDate) / 365.2425) D. Year(Today's Date) - Year(FirstOrderDate) Q8. Why does the instructor recommend using the Int function when calculating years as a customer? A. To always round up to the next year B. To remove the fractional portion and display whole years only C. To multiply years by 2 D. To truncate string values Q9. How is the customer's discount percentage determined in the final solution? A. With an immediate If statement hardcoded in the form B. By looking up the customer's years as a member and returning a matching discount from a table using DMax C. By manually typing the discount percentage next to each customer D. By basing it on the total amount spent over their lifetime Q10. What is the key advantage of storing discount brackets in a separate table instead of hardcoding them? A. It makes modifying or adding discounts in the future much easier B. It ensures discounts are applied randomly C. It prevents Access from displaying errors D. It requires less storage space Q11. What does the following DMin criteria ensure? CustomerID=CustomerID AND IsPaid=True A. The function looks up the smallest order date for all customers B. The function finds the first unpaid order C. The function finds the earliest paid order for the current customer only D. It always returns the largest date Q12. When using DMax to determine a discount, what should the criteria be? A. DiscountYears = YearsAsCustomer B. DiscountYears >= YearsAsCustomer C. DiscountYears <= YearsAsCustomer D. DiscountAmount < YearsAsCustomer Q13. Why might null values for CustomerID cause errors in DMin or DMax lookups? A. Null CustomerIDs are skipped by Access automatically B. D functions cannot receive a null as a search parameter and will display errors C. Null values are converted to zero automatically by Access D. The functions will treat null as a valid CustomerID Q14. What should you do to handle possible nulls when referencing fields in criteria for D functions? A. Always use the Mid function on the field B. Use the NZ function to convert null to zero or an appropriate value C. Avoid using criteria altogether D. Use only text fields, not numbers Q15. Why is it generally not a good practice to hard-code business rules like discount levels directly in functions or forms? A. It makes updating and maintaining the system harder B. It increases database speed C. It reduces the chance of errors D. It causes Access to crash Answers: 1-B; 2-B; 3-A; 4-A; 5-B; 6-B; 7-C; 8-B; 9-B; 10-A; 11-C; 12-C; 13-B; 14-B; 15-A 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. SummaryToday's video from Access Learning Zone covers how to determine a customer's earliest order date in order to calculate a membership discount, based on the total time they have been a member, using the DMin function in Microsoft Access.This question came from someone who wanted to set up a membership program where discount levels increase depending on how long the customer has been with the business. For example, if the customer's first order was more than one year ago, they get a 5 percent discount, over three years earns 7 percent, and five years or more qualifies for 10 percent. The goal is to display this information automatically on the customer's form. To start, it is important to understand aggregate queries, which allow you to calculate values like the earliest order date for each customer by using functions such as Min. Anyone unfamiliar with aggregate queries or the D functions—DLookup, DMax, and DMin—should review those foundational topics first, since DLookup is the simplest of the D functions, and understanding it makes learning DMin and DMax much easier. There is also an NZ function that is critical for handling null values so calculations do not result in errors. Assume a simple database structure with a customer table and an order table. Each customer can have multiple orders, and it is important to check that only paid orders are considered when looking up discount eligibility. The easiest way to find the earliest order date is by creating an aggregate query. You set up a query that joins the customer and order tables on customer ID, includes only records where the order is marked paid, and then groups the results by customer ID while taking the minimum order date. You can also use an alias in the query to rename the minimum value as something like 'FirstOrderDate' for clarity. To display the customer's first order date directly on their form, instead of running a full query every time, you can use the DMin function. The idea is to create a text box on the customer form and set its control source to a DMin expression that finds the earliest order date for the current customer and only for paid orders. The DMin function allows you to set multiple criteria in its argument, such as matching both the customer ID and ensuring the order is paid. If the current record does not have a value (for example when adding a new customer), DMin can throw an error. Wrapping the criteria using NZ helps avoid this problem by substituting a zero for any null customer IDs, so you get a blank instead of an error message on the form. After finding the first order date, the next step is to calculate the number of years the customer has been a member. This is done by subtracting the first order date from today and dividing by 365.2425 to account for leap years. If you want just the integer part of the result—the completed number of years—you can use the Int function, which removes any fractional part. Now, instead of hardcoding discount levels in your calculations, it is much more flexible to set up a discount table where each row defines qualifying years and the corresponding discount percentage. For example, rows in the discount table might say '1 year = 5 percent,' '3 years = 7 percent,' '5 years = 10 percent,' and so on. This allows you to change, remove, or add new discount levels without modifying any form logic. To determine the discount for each customer, use DMax to look up the highest discount where the qualifying years is less than or equal to the number of years the customer has been a member. The DMax function ensures that, if someone has qualified for several tiers, the maximum applicable discount is selected. You might occasionally run into errors if the value you are using as the criteria in DMax could be null. To avoid this, always use NZ to default any nulls to zero so the lookup completes smoothly. Throughout the process, I demonstrate my own troubleshooting steps so you can learn from any mistakes and understand how to correct common errors, such as not specifying an alternative value in the NZ function. Also, in today's Extended Cut, I cover how to identify a customer's second order date, which could be used for businesses with discount programs that depend on follow-up orders within a certain time frame. This is a more advanced topic but answers a real-world request from a user. If you would like access to more detailed lessons, including the extended cut and other exclusive content, you can find out more about membership benefits on my website. There are different membership levels depending on what kind of training and resources you need. If you enjoyed the lesson and want more, check out the links below for additional resources, free courses, or how to submit your own 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 ListUsing aggregate queries to find earliest order datesFiltering orders by paid status in queries Grouping orders by customer to get minimum order date Creating an alias for query fields Displaying earliest order date on a customer form Using DMin to find the first order date per customer Applying multiple criteria in DMin for paid orders Handling null values in DMin criteria with NZ Calculating years as a customer from order dates Using date arithmetic to compute membership duration Removing fractional years with the Int function Creating a discount table for membership discounts Using DMax to determine the maximum applicable discount Writing DMax criteria based on years as customer Formatting discount values as percentages on forms Error handling with NZ to prevent #Error in forms Testing discount logic by adding and adjusting records in tables |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access minimum value, min, dmin, dmin function, earliest date, earliest order, first order date, years as customer, discount lookup, 2nd order, second order PermaLink DMin in Microsoft Access |
