|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Age, Part 2 By Richard Rost 
5 years ago
Checking the Accuracy of Original Age Calculations In this video, we will examine the Age calculations in my original Microsoft Access Age video and see just how accurate they are. I built a database to run through 10 million date calculation iterations, and it turns out the original formula is 99.91% accurate. I will, however, show you a more advanced formula that is always 100% accurate. Rabid Follower (a YouTube Subscriber) says: I think dividing by 365.2524 could lead to erroneous results pretty often, and it's not "1 in 5000" like you said... It’s more like 1 in 486 from my calculation. MembersMembers will see exactly how I built my database to perform my calculations to check my accuracy.
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, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, calculating age part 2, age in access, 100% accurate age in microsoft access, datediff, dateserial
IntroIn this video, we will talk about the accuracy of age calculations in Microsoft Access by revisiting the methods discussed in my original age calculation video. We will compare two different approaches to figuring out a person's age using sample data in Access, look at the results from testing nearly 10 million date pairs, and discuss why the common formula is usually accurate enough for most applications and when you might need to use a more precise method. This is part 2.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this video, we're going to examine the accuracy of my age calculations from my original age video. Before we get started today, if you haven't watched my original "How to Properly Calculate Someone's Age" video, pause this and go watch that now so you understand what I'm talking about. Now that you've watched the original age video, today's video is in response to a comment on my YouTube channel about that original video from user rabid follower. Let me first start out by saying that I absolutely love well-thought-out comments like this. I love being challenged. If you think that something I have to say in one of my videos is incorrect, please speak up. Let me know. If there is something you think is out in left field, tell me. As long as you're polite and courteous to me, I will respond in kind. Rude people are given the boot. I don't tolerate any rude or insulting behavior or comments on my channel. But rabid follower's comments were well thought out, very intelligent, and very much appreciated, so thank you. So, here's what he said. He said, "I think dividing by 365 point whatever could lead to erroneous results pretty often, and it's not 1 in 5,000 like you said. It's more like 1 in 486 for my calculations." Let's examine this. Now, my reply in the YouTube comment thread was, as I said in the description and in the original video, this method is 99.9% correct. This method is good enough for most applications. I do have a VBA function in the extended cut of the original video, which I'm going to show you today, that's 100% accurate to the day. Here's a copy of the original thread. I'll put a link to it down in the links section below so you can click on it and read the whole thing if you want to, or just pause this and read it if you want. So, since he brought up that my calculations might be wrong, I ran some tests. If I'm wrong, I always admit it. What I did was I actually built a database to generate random dates. I would generate two random dates and then calculate the age of that person using both methods. The first one is the simple division equation that I show in the original video. The second method is the one that's 100% accurate that I know works 100% of the time, and that's the advanced calculation that I cover in the extended cut. After running through almost 10 million iterations, it turns out that the simple division age calculation is 99.91% accurate. That's after almost 10 million calculations. So, 99.91% of the time it's accurate. Therefore, roughly 1 in 1,000 calculations will be off by one year and they'll always come in low. So what is this 100% accurate calculation? Well, there's age 1 and age 2 by two different methods. Age 1 is what I showed you in the original video. Take date 2, subtract date 1, and then divide that by 365.2425. That will give you the result that is 99.91% accurate, which personally I think is good enough for most uses. If your database is mission critical and you need to have an accurate 100% of the time perfect result, then you have to use age 2. I like to keep my TechHelp videos as simple as I can for the majority of people, and I think for most users, especially business uses, age 1 is just fine. I've been using it for years with no complaints whatsoever. I didn't want to have to take the time to go through and explain DateDiff and DateSerial. Even though I do have other free videos on how to use those, I wanted to keep the age calculation simple for the majority of people. That's why I explained in the extended cut. I'll cover these in more detail for those who want to really learn the ins and outs of the 100% accurate formula, but it takes knowing some other stuff. I felt age 1 was also good enough because the original questioner was checking IDs in a nightclub. So, even if it's wrong, it's going to come in low. A 21-year-old might accidentally show up as 20 but never the other way around. You're never going to admit someone who's 20 when the calculation shows 21. So, a quick check of their ID will fix that situation, which is why I think for that user, age 1 is just fine. Did I exaggerate in the original video? In the original video, I said that age 1 is accurate. I said 99.99% of the time. I think it would give you an error like one out of 5,000 years. I didn't actually do the math. I was just trying to make the point that this method is good enough for most uses. It's like when you're talking to someone and you just off the cuff say, "Yeah, 1 out of a million blah blah blah." But the actual accuracy is right there - 99.91%. I did the math this time. So, rabid follower, I'll split the difference with you. You said 1 out of 486. I said 1 out of 5,000. That's roughly half. That's how I do my math. Let's see this new function in action. Let's see how it works. Not really a function, it's a calculation. You can make it a function if you want to, but again, that involves some VBA and looks more advanced. I try to keep things nice and simple as much as I can for the TechHelp videos. The extended cuts can get a little crazy though. Let's make a table and just see both of these side by side in the query. This is my TechHelp free template. If you want to go download a copy, this is my website. It's free, absolutely free. You can go grab it. If not, you can put this in any old database. So, create and then table design. Do you need an ID for this table? No, not for this one. So just D1, that'll be a date. And then D2, that'll be a second date. And you're not getting lucky on that third date, so don't even ask. Let's save this as myAgeT. And go ahead and fill the primary key in there for me, I don't care. Let's go over to datasheet view and just put a couple of dates in here. I'll put in 10/23/72, that may or may not be my birthday, and today's date, so I'll hit Control Semicolon and just throw that in there. We'll put some more records in just a second. Let's go over to make a query now. Create, query design, myAgeT. Let's bring in D1, D2. Now, the original calculation right here - there it is, straight out of the first class, first video, whatever you want to call it. I'll zoom in so you can see it, nothing fancy. Date 2 minus date 1, divided by 365 point whatever. Run that query now and you'll see, yeah, I'm 48, that came out right. Back to design view, and let's make age 2 now. I'm not going to explain this whole calculation. If you want to learn that extended cut for the original one, I go through it in detail. There it is, I will zoom in so you can get a good look at it. Type it in if you want to. Basically, it uses DateDiff and DateSerial. I have other free videos that cover how DateDiff and DateSerial work. I'll put links down below if you want to go learn how these work. If not, there's the calculation, and that is accurate 100% of the time. Save this as myAgeQ and run it, and there we go, there's age 48. Now, rabid follower brought up that if you've got some dates that span leap years a certain way, then the original calculation, age 1, might not be right. He's correct. For example, let's see some of his dates here. 4/1/2019. Actually, let's put the old one in first, 1998, D1 has to be the older one for this calculation to work, and then 4/1/2019. This person would have turned 21 on 4/1/2019. In 2018, they were 20. So this is one of those 1 in 1,000 calculations that don't come out right. It spanned just the right leap year to not work. But it will always give a low answer if it's incorrect - it'll be a year too low. So for, like I said before, a bar or restaurant type scenario, this is just fine. So I'm just going to go, "Hey, I'm actually 21. Here's my ID. Oh, okay, you're good." If your need is mission critical, use this guy. I hope this helps to clear things up a little bit, and rabid follower, thank you very much for your very thoughtful and well laid out argument. I appreciate it. If anyone wants to see how I built my testing database, I'll cover that in the extended cut for members. Silver members and up get access to all of my extended cut videos, and there's lots of them now - that's over 100. I'll show you how I built this form, how I set everything up in VBA with the loops and the calculations and the moving parts and all that good stuff. 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 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. 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 liked 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 one dollar and 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 prompted the creation of this TechHelp video?A. A user comment challenging the accuracy of the original age calculation video B. A new feature update in Microsoft Access C. A bug reported in the TechHelp template D. A request for a VBA tutorial Q2. What basic method for calculating age was discussed in the original video? A. Using DateDiff and DateSerial directly B. Subtracting one date from another and dividing by 365.2425 C. Using a third-party Access plugin D. Manually counting leap years Q3. According to Richard, how accurate is the "simple division" age calculation method after nearly 10 million tests? A. 95% B. 100% C. 99.91% D. 99% Q4. In what scenario did Richard suggest that the simple age calculation method is usually sufficient? A. For checking IDs in a nightclub or bar B. For calculating retirement pensions C. For legal age verification in court D. For tax filing with the IRS Q5. What is a drawback of the simple division age calculation method? A. It always overestimates the age by one year B. It requires advanced VBA knowledge C. It can underestimate the age by one year in rare cases D. It fails completely if used with leap years Q6. When a miscalculation occurs with the simple division method, what is always true about the error? A. It will overstate the age by one year B. It will understate the age by one year C. It will show the correct age D. It will reset the calculation to zero Q7. What does the advanced, 100% accurate age calculation use in Access? A. Only arithmetic division by 365 B. DateDiff and DateSerial functions C. A manual process of counting years D. A data export to Excel Q8. Why did Richard not include the advanced formula in the original, free TechHelp video? A. He believed most users would prefer a simple solution for general purposes B. The advanced formula did not exist at the time C. It only works in Excel, not in Access D. He wanted to keep the video under one minute Q9. How can viewers access more advanced or extended cut content from Richard? A. By joining as Silver or Gold members on his YouTube channel B. By subscribing for free on his mailing list C. By watching advertisements to the end D. By requesting access via email Q10. Which of the following is true about Richard's approach to handling errors or corrections presented by viewers? A. He ignores negative feedback B. He blocks all commenters automatically C. He appreciates well-thought-out and polite comments and admits when he is wrong D. He refuses to update his videos under any circumstances Q11. What benefit do Gold members get in addition to Silver member perks? A. Access to the code vault and downloadable sample databases B. Access to beginner courses only C. Free merchandise and stickers D. Access to all videos commercial-free Q12. What calculation always gives the correct age, even across leap years? A. Date 2 minus Date 1 divided by 365 B. DateDiff combined with DateSerial C. Random date generation D. Counting weekdays between dates Answers: 1-A; 2-B; 3-C; 4-A; 5-C; 6-B; 7-B; 8-A; 9-A; 10-C; 11-A; 12-B 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 focuses on evaluating the accuracy of the age calculation method I presented in my original video on how to properly calculate someone's age in Microsoft Access.If you have not seen the original "How to Properly Calculate Someone's Age" lesson, I highly recommend watching that first so you have the proper background for today's discussion. This video was prompted by a thoughtful comment left on my YouTube channel. The commenter suggested that dividing by 365.2425 to determine age might result in errors more often than I had previously stated. I genuinely appreciate insightful feedback like this. If you ever believe something I have shared is incorrect, let me know. I am always open to reviewing my methods, provided the discussion remains respectful and courteous. Disrespectful behavior will not be tolerated, but well-reasoned arguments are always welcome. The main point raised in the comment was that my claim of a one-in-5,000 error rate was likely an understatement. According to the commenter's calculations, the error occurs roughly once in every 486 cases. I addressed this in my reply, clarifying that I have always maintained the division method is 99.9 percent accurate, which is usually sufficient for most applications. In the extended cut of my original video, I showed a more advanced VBA method that is 100 percent accurate to the day. To verify these claims, I created a database to generate random pairs of dates and compared the age calculations using both methods. After nearly ten million test runs, the division method proved to be 99.91 percent accurate, which translates to about one error in every 1,000 cases. When the error does occur, it always results in an age that is one year too low. Here's a summary of both methods: - The first method, presented in my original video, involves subtracting the birthdate from the current date and dividing by 365.2425. This method is quick, straightforward, and accurate 99.91 percent of the time, making it a good choice for most business scenarios. - The second method, which I cover in detail in the extended cut, uses DateDiff and DateSerial functions for complete accuracy. If you are building a mission-critical system that requires precision in every instance, you should use this approach instead. Most of the time, the simpler method is sufficient. For example, in environments like nightclubs where IDs are being checked, a small error that always rounds down is not a significant problem. If someone appears a year younger due to the calculation, a quick check against their ID resolves the issue, and the error never leads to someone being let in when they should not be. In the original video, I had estimated the error rate at about one in 5,000. The actual tested value, 99.91 percent, comes out to about one in 1,000. This small difference is unlikely to matter in most practical contexts, but for those who require absolute accuracy, the second calculation method is essential. To help illustrate the difference, I demonstrated how to set up a simple table in Access, input dates, and see both age calculations side by side in a query. I kept the instructions concise so that even beginners could follow along without needing advanced knowledge of VBA. For those interested in more details about the formula using DateDiff and DateSerial, I have additional videos available that explain these functions thoroughly. The reason the quick calculation fails in about one out of every 1,000 cases is that it does not always handle leap years correctly, as the commenter pointed out. Specific pairs of dates that span certain leap years can produce a result that is one year too low. However, as mentioned earlier, this is rarely an issue in typical business cases. For members who are curious about how I built the database to test these calculations, I will demonstrate the setup and VBA code in the extended cut, accessible to Silver members and higher. The extended cut shows how to construct the form, design the testing process, and automate the iterations with VBA. Membership on my site comes with various benefits. Silver members gain access to all extended cut TechHelp videos and live chat sessions. Gold members receive those benefits plus access to all the sample databases I create and my code vault. Platinum members enjoy all previous perks as well as full access to my complete course library, including topics beyond Access. Rest assured, these TechHelp videos will always remain free. I intend to continue producing them for as long as there is interest. If you find the content helpful, please subscribe to my channel and consider leaving feedback in the comments section. I do read all comments and appreciate your support. For additional resources, including links to related videos, free courses, or further reading, be sure to check the description below the video. If you wish to receive notifications when new videos are released, consider joining my mailing list since YouTube no longer sends email updates. If you have not yet tried my free Access Level 1 course, I encourage you to do so. It covers the fundamentals of database creation in Access and is available both on my website and on YouTube. Level 2 is just a dollar or free for members of any level. If you want your question answered in a TechHelp video, visit my TechHelp page and submit your inquiry. You can find the complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListEvaluating age calculation accuracy in AccessComparing simple and advanced age calculation methods Creating a table for age calculation in Access Entering and testing dates for age calculation Building a query with the simple age formula Understanding accuracy of division-based age calculation Demonstrating when the simple formula can be off Adding an advanced, accurate age calculation using DateDiff and DateSerial Testing special cases where leap years affect accuracy Deciding when to use simple vs. advanced methods in real scenarios |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access calculating age part 2, age in access, 100% accurate age in microsoft access, datediff, dateserial PermaLink Age, Part 2 in Microsoft Access |
