|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Moving Average By Richard Rost 
5 years ago
Calculate a Moving Average in Microsoft Access In this video, I will show you how to calculate a standard moving average (also called a rolling average, moving mean, or rolling mean) in Microsoft Access. Windle from Williamsburg, Virginia (a Platinum Member) asks: can you show me how to do a moving average in Access? MembersMembers will see how to calculate the average over the last five periods instead of just the last five days. This is necessary if you have gaps in your dates (missing data on the weekends, for example).
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, moving average, moving mean, rolling average, rolling mean, simple moving average, dlookup, davg
IntroIn this video, I will show you how to calculate a moving average, also called a rolling average, in Microsoft Access. We'll go over what a moving average is, create a sample table with date and value fields, and build a query using the DAvg function to generate a moving average over a set period, such as five days. I will also cover formatting the results as currency and discuss key Access features like calculated query fields and the importance of understanding DLookup and string concatenation for this process.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to calculate a moving average, also called a rolling average, in Microsoft Access.Today's question comes from Windle in Williamsburg, Virginia, one of my platinum members. Windle asks, can you show me how to calculate a moving average in Access? Well, of course I can. Let's see how. In preparation for this video, I just earlier today did a moving average video in Excel. In case you do not know, a moving average is just looking at the previous number of periods. For example, you could do a five-day average. We say look at those five days, then look at the next five days, then look at the next five days, and generate an average for each period of the previous five days, or seven days, or ten days, or whatever you want. It is used all the time, for example, in the stock market, calculating the previous 30 days moving average. In Excel, it is super easy. Just come right here because you can't do them for these days. You come right here and you say equals average, and then the previous five days, then press enter. When you autofill this guy down, it gives you - and we are going to ignore these errors - it gives you a moving average right there. See that? Average of that cell and the previous four, so it is a five-day moving average. This is super easy to do in Excel because in Excel, you can see right here, here are the previous five days. In Access, it is a little trickier, but it is still not that hard to do. Let's see how to do it. First up, some prerequisites. I want you to go watch these two videos first if you do not know how to do this stuff. You can see the calculated query fields. That is where you take a column in a query and you make it based on some other values. And the DLookup function, if you do not know how to use DLookup for sure, go watch that video. We are going to use another function today that is related called the average to calculate a moving average, but DLookup is its close cousin, and it is a lot easier to understand DLookup first, then move into the other D functions. There is DAvg, DSum, DCount, DMax, DMin, but DLookup is like the granddaddy of them all. OK, so here I am in my TechHelp free template. This is a free download from my website. You can go grab a copy if you want to, but what I am going to show you today really doesn't matter. We are just going to make a new table anyway. I will start out also by telling you that we are going to do a standard moving average. There is also something called an exponential moving average, which smooths out the average even more, and gives more consideration to recent data than old data. But we are just going to do a standard moving average in this video. If you want to see the exponential one, give me a comment, send me an email, let me know, and if enough people are interested, I will do a video on that too. We do not need the main menu for this one. Let's create a new table, and we'll just create a simple table that's got an ID field, it has a date, and it has some value. So we have the ID, that is an autonumber. Do you need that? No, it is a good idea to have it, but you do not need it. My date, that will be a date value. You can't use the word "date" there for a field, remember, that is a reserved word, don't just use the word date. That is why I always have to put "my" in front of it. Then my value, whatever that happens to be, whether you are doing stock prices, or sales, or whatever. In fact, I believe Windle is doing COVID test results. We will get more into that in the extended cut. I will save this as just "my table." Doesn't really matter, primary key, sure. There we go. Let's go to datasheet view. Let's put some sample data in here. We have one, one, and a value, I do not know, $10. One, two, some other value, 15. I am going to put 10 days worth of data in here. There we go. I got from January 1st to January 10th, and some values. Now, let's close this down, and let's go over and make our query - create query design. Let's bring in that table, "myT." I will bring in the star, so I can see all the values. Now, for a calculated field here, let's calculate the average of the "myValue" field. Let's just see them all first. We will call this "x," and that is going to be the DAvg. Let me zoom in so you can see that better. Shift+F2. DAvg. What am I averaging? I am averaging the "myValue" field. That is my currency. From "myT." Close that up just like it is. Hit OK. Then we are going to save this. I will call it "myQ." Now, let's call it "moving." You want to have to rename it. This is going to be our moving average query. OK, and then run it. There you can see there is 12.3. If I added all these up and divided by 10, I should get 12.3. Yep, a quick check with the calculator says that's right. That is just the average of all of them, and it is putting it in every single row. What I want to do here is I want to check and say, OK, take this guy, and I want the average of the five items, the five entries, before you. Basically, I am going to say, give it to me where the date is greater than five days ago and less than or equal to today. How do I do that? Let's go back to design view. Again, I will zoom in down here. We are going to put a criteria on here. Criteria is my date is greater than. Now, this is going to be an actual date value put in here, so I have to enclose it inside the little hashtags, the number sign. My date minus five, that is five days ago. Remember, in Access, one day is equal to one. Five days ago is minus five. If you do not know this little ampersand stuff, it is called string concatenation. I probably should have put that in the prerequisites. I will add it to the list now. I assume if you do the other two, you know concatenation. That is where you put two strings together with the ampersand sign. Greater than my date minus five, finish by enclosing it with the other pound sign. My date is less than or equal to hashtag and my date and close it up. See that? Give me the DAvg of "myValue" from "myT," where my date of the current record is greater than five days ago and less than or equal to today. Hit OK. There it is. Let's save it, and then run it. There you go. What is going to happen here is this first one is only going to be that value, because it is looking for five days ago and then averaging it with just the one. The average of just the number 10 is 10. Here we get the average of these two. This one is going to give you the average of those three, four, and the first one you can actually get five results for is that. A quick check shows that is correct. If you want to format these, design view, format this as currency, right click, properties, format, currency. Sometimes it does not show up in the drop-down. Run it. There we go. Sometimes it does not even allow you to do that when you have a calculated field. Sometimes you have to also put this guy inside the format function, format that whole thing, comma, as currency. That should do it. Let's see. There we go. It is weird. Sometimes the query properties do not give you the right format in here. This is an easy way to calculate a moving average if you have concurrent days, if you do not have any gaps in here. But what if you do have gaps in there and you want to check the past five days? Let's say you are doing weekdays. You are in the stock market, then obviously Saturdays and Sundays you are not going to have any data. So you have to check the previous five entries, not necessarily the previous five days. How do you do that? I will cover that in the extended cut for the members. If you want to learn more, in the extended cut for members - 25 minutes long - I am going to show you how to calculate moving averages if you have gaps in your dates. So you are missing like 1/3, 1/4, you are missing your weekends. It will not be an issue. I will show you how to count the previous X number, five number, whatever of entries, not necessarily dates. The actual original question was from one of my platinum members who is doing positivity testing for COVID. He wants to be able to look at the previous X number of days, positivity rates, calculate the actual positivity, get a moving average of that. Then I will show you how to take that value and put it on a form so you can see the most recent results on the form. That is all covered in the extended cut. Like I said, 25 minutes long. Silver members and up get access to all the extended cut videos. Gold members can download these databases. How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me. You will get one free expert class each month after you finish the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more. You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website. But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free. QuizQ1. What is a moving average?A. An average that calculates over a specified number of preceding periods B. An average of all values in a dataset C. An average calculated only for the most recent value D. An average that predicts future values Q2. Which function in Access is primarily demonstrated for calculating moving averages in the video? A. DCount B. DAvg C. DMin D. DSum Q3. Why is calculating a moving average easier in Excel than in Access? A. Excel has built-in moving average formulas B. Excel allows direct reference to previous rows in the worksheet C. Excel automatically creates queries for averages D. Access does not support any average calculations Q4. What is the main prerequisite knowledge recommended before watching the moving average tutorial in Access? A. Using the Format function in queries B. Understanding DLookup and calculated query fields C. Understanding macros in Access D. Working with Access security settings Q5. What does the DAvg function require as its first argument? A. The table name B. The field to average C. The field type D. The number of records Q6. Why might you avoid naming a field "date" in Access tables? A. Because "date" is a reserved keyword in Access B. Because Access cannot store date values C. Because it causes formatting issues D. Because "date" is a protected system field Q7. How do you calculate the moving average for the last five days in Access, based on the video? A. By selecting all records and using DSum for the past five days B. By using DAvg with a criteria that filters records to those where the date is within five days prior to the current record C. By creating a separate table with just five recent records D. By using Excel to perform calculations and importing results Q8. In Access, what is the purpose of using pound signs (#) in a DAvg criteria? A. To designate a currency value B. To indicate a string value C. To enclose date values D. To enclose field names Q9. What does the ampersand (&) operator do in an Access query expression? A. Divides two numbers B. Concatenates strings together C. Multiplies two numbers D. Subtracts one date from another Q10. What is a limitation of the basic moving average method shown in the video? A. It cannot format values as currency B. It only works if there are no gaps in the dates C. It only works for text fields D. It does not support more than 10 records Q11. What is an exponential moving average, as briefly mentioned in the video? A. A moving average calculated using only future data B. A moving average where recent data is weighted more heavily C. A moving average that uses strictly equal weighting for all data D. A method not available in Access Q12. If weekend dates are missing from your data (such as in stock market data), what adjustment must be made to the moving average method? A. Use only DSum function instead of DAvg B. Calculate a moving average over the last X entries, not X days C. Only average the values for Friday and Monday D. Remove all weekends from the table Q13. Which Access function is a "close cousin" to DAvg as discussed in the video? A. DCount B. DLookup C. DMin D. DMax Q14. If the formatting as currency does not show up properly in the query, what workaround is suggested? A. Recreate the table field as Currency data type B. Wrap the calculation in the Format function as currency C. Set the format at the form level only D. Use Excel formatting upon export Q15. Who gets access to download all the sample databases discussed in TechHelp videos? A. Bronze members only B. Silver members and up C. Gold members and up D. Platinum members only Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-A; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-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. SummaryToday's video from Access Learning Zone covers how to calculate a moving average, also known as a rolling average, in Microsoft Access.The question was whether it is possible to calculate a moving average in Access. To give some background, a moving average is simply the average value calculated from a specific number of consecutive periods. For example, you might calculate the average for the last five days, then move forward one day and calculate the average for the next five, and so on. This technique is especially common in stock market analysis, often using a 30-day moving average, but it is useful in any situation where you want to smooth out data trends over time. If you are familiar with Excel, you know this is very straightforward there. You can use the average function on a range of cells, and then autofill down to get the rolling average for each period. In Excel, you clearly see the previous five or ten days right next to each other in the spreadsheet, and it is easy to reference those ranges directly. Access is a bit trickier since you cannot refer to "previous five rows" as easily, but with the right tools, it is still entirely doable. Before we start, I recommend making sure you are comfortable with calculated query fields and with the DLookup function. If you are not, you should review those topics first. Understanding DLookup will make it much easier to learn the other "D" functions like DAvg, which we will use for the moving average. For this lesson, I am working with my free TechHelp template, though you can follow along with any database since we will build a new table for the demonstration. We will focus on a standard moving average, not the exponential moving average that gives more weight to recent data. If you have an interest in exponential moving averages, let me know and I can cover that in the future as well. To begin, I set up a simple table with three fields: an ID (as an autonumber), a date field (always avoid using "date" alone as a field name because it is reserved in Access, so prefix it with something like "my"), and a value field, which could represent prices, test results, or any metric you need. After adding some sample data covering ten days, I move on to creating a query. The goal is to add a calculated field that uses the DAvg function to calculate the average value from our table. By default, DAvg just gives you the average of the entire dataset, which is a good check to make sure the function is working. However, what we want is the moving average. So, I adjust the expression to capture only the records from the current date back five days. In Access, you work with date criteria using the "#" characters to designate date values and perform operations like "greater than" or "less than or equal to." Access treats each day as one unit, so "date minus five" takes you five days back. Using string concatenation with the ampersand allows you to build these expressions. If you are unfamiliar with concatenation, it is where you combine multiple strings together, something that is helpful throughout Access. With the criteria set properly, running the query yields the desired moving average. The first few records reflect averages over however many records exist at that point, and once there are enough records, the five-day window kicks in. If you prefer to see the values formatted as currency, you can set that in the query's properties or use the Format function directly in your calculated field, in case the standard formatting does not appear as expected. This method works well when your data includes every consecutive day in the period. However, in situations where there are gaps, such as weekends being excluded from stock data, you may want to instead average over the last five entries rather than the last five dates. This scenario is addressed in the Extended Cut video for members. In the Extended Cut, I show how to calculate moving averages when there are missing dates in the dataset, such as weekends or holidays, ensuring you are always working with the previous set X number of entries. I also go through how to put the calculated moving average onto a form, so you can display the latest results live. The original member question related to calculating COVID test positivity rates using moving averages is covered in more detail there. If you are interested in accessing the Extended Cut, which is 25 minutes long and goes into more advanced techniques, you can learn more about becoming a member on my website. Silver members and above have access to every Extended Cut TechHelp video, a monthly free beginner class, and other perks. Gold members can also download sample databases and get access to the code vault, plus priority for questions. Platinum and higher-level members receive all those benefits, higher question priority, and access to all full-length beginner and developer courses, across a variety of subjects like Access, Word, Excel, and Visual Basic. As always, the free TechHelp videos will keep coming. You can visit my website for the complete video tutorial with step-by-step instructions on everything discussed here. Live long and prosper, my friends. Topic ListExplanation of moving averages in AccessSetting up a sample data table with date and value Using DAvg function to calculate averages in a query Creating a calculated query field for moving average Applying date criteria in DAvg for a 5-day moving average Formatting calculated fields as currency in queries Handling string concatenation in Access expressions Understanding how moving averages work with continuous dates |
||||
|
| |||
| Keywords: TechHelp Access moving average, moving mean, rolling average, rolling mean, simple moving average, dlookup, davg PermaLink Moving Average in Microsoft Access |
