|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Month-Over-Month By Richard Rost 
5 years ago
Compare Month-Over-Month Sales in Access In this video, I will show you how to compare sales month-over-month for both the previous month and the same month last year in your Microsoft Access databases. Jeremy from Colorado Springs, Colorado (a Platinum Member) asks: I need to compare sales month-over-month for both the previous month and for the same month last year. How can I do this in Access? MembersMembers will learn how to perform the same calculations in Access VBA. You will be able to specify any two periods with begin date and end date, so you can check month-over-month, year-over-year, quarter-over-quarter, etc.
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, month to month, month over month, year over year, quarter over quarter, sales comparisons, month function, current month vs previous month, growth, month-over-month, date period comparisons, year-over-year, monthly percentage growth, percent increase month on month, percentage change calculator, growth rate formula
IntroIn this video, we'll learn how to compare month-over-month sales in Microsoft Access, including both current month to previous month and current month to the same month last year. I'll show you how to build the necessary aggregate queries, use functions like DSUM, NZ, DateSerial, and DateAdd, handle date formatting, and calculate percentage growth while avoiding common pitfalls like divide by zero errors and parameter prompts. This step-by-step walkthrough uses sample sales order data to demonstrate how to display month-over-month changes cleanly in your Access queries.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to learn how to compare month over month sales in Microsoft Access, both current month to previous month and the current month to the same month last year. So, either January to February or January to last January. We're going to do both of those calculations in this video. Today's question comes from Jeremy in Colorado Springs, Colorado, one of my platinum members. Jeremy says, "I need to compare sales month over month for both the previous month and for the same month last year. How can I do this in Access?" Well Jeremy, we can do this in Access with a query. It's going to require a bunch of functions though. Here's a list of all the stuff you have to know before continuing with this lesson. I've got free videos on all of this stuff. I'll put them in the links section in the description down below the video. You have to know how to use aggregate queries, calculated query fields, the if function, date serial, date add, dSum, and NZ. We're going to use all those functions today. If you don't know how to use any of those things, go watch that video now. Pause this one. Go watch the other video then come back here. To calculate month over month sales, it doesn't matter whether the previous month is the one you're checking - whether you're comparing January to February, or whether you're checking the same month last year, such as January to last year's January - the calculation is the same. It's basically month two minus month one divided by month one. So, if February is $100 and January was 50, you divide that by January sales, you get 50 divided by 50, which is one, which in Access terms, when we format that as a percent, comes out to 100. So, 50 to 100 is 100 percent sales growth. The trick in Access is figuring out how to get the previous month's or last year's same month's calculations. That's where the dSum function comes in. Let's see how we set this up in Access. Here I am in my database. This is my TechHelp free template. You can download a free copy of this database if you want from my website. There's a link down below. In my database, I've got a customer form and each customer can have orders. Here's my orders. In this database, the orders are based on two tables: you've got the order table and the order detail table. If you haven't watched how I built this database, go watch that video now too. That's called my invoicing video. Again, I'll put a link down below. Since the order total is actually a calculation, it's adding up all of the line items on the order. We're going to need an aggregate query to bring these two things together to give us that order total. How do we do that? We have an order detail query over here that calculates that extended price. If you watch the invoicing video, you'll know what this is. It's basically quantity times unit price. That's the total for each line. Now we can create a total query, an aggregate query, to bring together all of those line items for each order. Go to create, query design, bring in my order table and my order detail query so I get that total over there. Now I can bring in order ID, the order date, and the extended price. Turn this into an aggregate query - right over here with the Totals button - and change extended price to Sum. If I run that query, you can see there, order one, order two, order three, and the total for each. This is the query you'll normally use for the rest of the calculations. But for the sake of class, I'm not going to do that. I'm just going to go into my order table and I'm going to cheat, just to make the class easier instead of having to type in a whole bunch of line items and stuff. We're just going to add an order total in here. This is just for class purposes. So, order total, and then a currency. Save that. This just makes it easier for the class. Put in some values in here. There, I typed in some extra orders. I put some order totals in here. So I've got some orders from January of 21, January of 20 (so we can do the previous year), and I've got March and February of 21 as well. If I want to compare February to January, I can compare those, and I can also compare January of this year to January of last year and the year before. Just put some sample data in here. See why I'm doing this? It just makes it easier. Otherwise, I have to put a bunch of orders in, then I have to go and put a bunch of order details in. This would basically be the same thing that your aggregate query will spit out for you. This is my list of orders with the order date and the order total. That's what's really important here. The next thing that I want is another aggregate query to bring together all of the sales from each month - each year and month together in another aggregate query. Go to create, query design. This time I'm going to bring in just the order table (or you'll bring in your other aggregate query, whatever you're using). The first thing I want is to split off the year. So I'm going to call this field Y and I'm going to use the year function: Year([OrderDate]). Those are some other functions that I should have put on the prerequisite list, but they're real simple to use. Year gives you the year of something, month gives you the month of something, day gives you the day of something. Then we need in the next column the month. We're going to split these off into their own columns. M is the month of [OrderDate]. If I run this now, just to see what I got, see how it splits off - that's each order. The first order is January 21, January 21, March 21, and so on. You can sort these if you want to. Sort these ascending so they go in order. Now all your orders are sorted. Now I want to group these together. So each year and month, when you have a unique year-month combination like these two, they'll be grouped together. We're going to turn this into an aggregate query. Now we're going to add the sum total of the orders for each group. So change that to Sum. Group by this and sum each group. Run that and there we go. There's the sum of all of our orders for each of these year-month combinations: January of 2019, January of 2020. Normally a business would have every month represented here, but this is just a sample set of data: January, February, March of 2021. Let's save this query. This is going to be my OrderTotalSumQ. Now I could take OrderTotalSumQ and break this down a little bit more. From this I can look at any record here - you've got the current month, which is 2 of 21. I know what the current month sales are. How do you figure out the previous month's sales? Well, you have to look at one less than that. Unless that is one, in which case you have to look for a 12 of the previous year. That's where it gets complicated. We're going to put this in a second query, make another query. Sometimes it's easier if you break this stuff up into multiple queries. I know I don't like having tons of queries in my database, but if you don't want to pull your hair out trying to put this in one query, it's easier to break it up. Sometimes I'll use multiple queries to break something up and then I'll try to figure out if I can put it all together in one query. Usually you can't when you're dealing with aggregate queries, sometimes you have to have multiple queries. You calculate the results from one query and then use that to feed a second query. So don't be afraid to use three, four, five queries if you have to in a row. As long as you get the right results, what does it matter? Let's create another query here. Create, query design. Love my queries. Let's bring in that OrderTotalSumQ. That's all we need. Close that. Bring in the year and the month. Just bring those right down here. Bring in SumOfOrderTotal, but I'm going to rename that - I'm going to alias it. I'm going to call this SalesThisMonth, just so we don't get confused. If I run that, I just see it says SalesThisMonth. That's now the name of this field. Now we have to figure out what the previous period is - the previous year and month. Let's start with the previous year. Let's call this Y2. That'll be our previous year. It's actually the year for the previous month. We're going to start by doing just the month over the previous month. Y2 is going to be equal to Y unless the month is a 1, in which case we have to back it up. So it's going to be: if M equals 1, then it's got to be Y minus 1, otherwise just Y. In other words, if the month is January, the year is going to be the previous year. Otherwise, it's going to be the same year. If M is 6, then the previous month period is May (M = 5). But if M is 1, then we've got to go back to the previous year to get December of the previous year. Let me zoom in so you can see that too. If M is 1, then go back a year, otherwise it's the same year. Now the month is going to be very similar. Let's call this M2. Here I'll zoom in and I'll type this in. M2 is going to be: if M equals 1, then 12, otherwise M minus 1. If the month is 1, set the previous month to 12, otherwise go back a month. So if it's June, go back to May. If it's January, go back to December. Let's save this. I'm going to save this as my MonthOverPreviousMonthQ. Let's just take a look at what we got so far here. January of 19 goes back to December of 18. January of 21 goes back to December of 20. February of 21 goes back to January of 21. So now we know the previous period's month and year. Now, knowing that, we can put together the begin date and the end date for that period, which will make the looking up of the sales a whole lot easier if we do it this way. Next field - here we go. Zoom in on it. This one's going to be called BeginDate, and it's going to be DateSerial(Y2, M2, 1). The begin date is going to be DateSerial, which if you watch the DateSerial video, it explains how to do this. You have year, month, day. So it's going to be the first day of that period. Run it. There's the first day of this period. Now we need the last day of the period. I don't want to have to figure out how many days are in each month, but Access knows that for us, fortunately. So all I have to do is add a month to this and then subtract a day. If I add a month to January 1, I get February 1. Subtract one day and now I'm on January 31. EndDate is going to be: DateAdd("m", 1, BeginDate) - 1. Add one month to BeginDate, subtract one day, minus one. Remember, a unit of one is equal to a day in Microsoft Access. Run it and there's your end date. Now I can look up the sales for that period and that's period two. We already know period one sales, here's period two - January 1 to January 31. Again, I did a whole other video on this and I'll put a link down below as well. The name of the video is called "Between Is Wrong." Basically, if you use BETWEEN and you say give me the sales between January 1 and January 31, it looks at midnight on both of those days. So if you've got times in your orders then you have to make sure to strip those times off before you do these calculations. You need to chop off that time because I personally like to know what time of day a sale comes in so I can look for trends and see that most of my orders come in between three and five p.m. So I do have the date and time stored to my order date. So you've got to strip those off before this works. That's not hard to do. Otherwise, if you've got sales coming in on January 31 at 2 p.m., it's not going to be included in that range. I'll put a link to that video down below. It's called "Times in Orders." Now here comes our dSum function. We're going to dSum. We're going to sum up the sales between those two dates. Inclusively, we're going to include the endpoints. Design view, here we go. It's going to be a big one, ready? PreviousMonthSales: dSum("OrderTotal", "OrderT", "OrderDate >= #" & [BeginDate] & "# and OrderDate <= #" & [EndDate] & "#") See that? Look up the OrderTotal from the OrderT where the OrderDate is greater than or equal to BeginDate and less than or equal to EndDate. You could use BETWEEN in here but I prefer this way. Now just in case this returns a zero or NULL, you want to make it a zero. So, NZ this: NZ(dSum(...), 0). Always wrap your D domain functions (dLookUp, dSum, dCount) inside of NZ functions. One more step. I'm going to format this as currency. Sometimes you can't right click on a column in a query and format it as currency depending on what gets returned. It just doesn't work and in this case it doesn't - you have to put the FormatCurrency on there. Ready? Let's save this. Run it and there we go. Now this period doesn't have a previous period so it comes back zero. There are no sales from 12 of 19. The first month that actually has sales in the previous month is right here. February of 21 has sales in January of 21 and it brings back the 300. Down here, March of 21, the previous period is February of 21 and it brings back sales of 600, which matches that. Now we can calculate the difference in sales. Difference is going to be: SalesThisMonth - PreviousMonthSales. Run it. It's just an example for class. You can rename it if you want to. There's the difference. All these are positive: last month was zero, this month is 20 so you're $20 up. $90 up. This one is $300 over the previous period. 600 minus 300, 1200 minus 600. Now we can calculate the percentage. PercentDifference is going to be (SalesThisMonth - PreviousMonthSales) divided by PreviousMonthSales. Hit OK. Take a peek. Now, 100 percent. We're going to format this as a percentage in a minute. Notice a divide by zero error up here. You don't usually want to see that in a report. So what we're going to do is say if there are no sales, you can't divide by zero. If there are no sales, we're just going to return a zero here. How do we do that? If PreviousMonthSales equals zero, then return a zero, otherwise return the calculation. Now, sometimes you can get away with right click, properties, format, percent. Sometimes you can, sometimes you can't. If you can't, you have to use the Format function. When I run it, sometimes this comes up: "Enter Parameter Value" thing. This is probably the number one question that people ask me, and I just goofed. Notice I spelled PreviousMonthSales - I forgot the "h" in "Month". People often ask, "I'm getting this Enter Parameter Value, I did everything you did in the video." Well, yeah, you spelled something wrong. Nine times out of ten, you spelled something wrong. I'll put this video in the link section too, my "Enter Parameter Value" video. I guarantee you that's what it usually is. So I just have to come in here and fix this - PreviousMonthSales - and there you go. Now, this query has a lot of information in it. We don't need to see all that. I'm going to use another query to break that down and make it look a little prettier. Let's make one more query. Create, query design, bring in that MonthOverPreviousMonthQ. First off, let's make CurrentMonth but we're going to make it display nicer. We have year-month. CurrentMonth or MyMonth, whatever you want to call it. It's going to be: Y & "-" & MonthName(M). MonthName is a handy function that returns the name of the month. If you run it, you get that. If you only want to see the left three characters of that, you just use Left(MonthName(M), 3). Give me the left three characters of the month name. Next column, SalesThisMonth. Next, PreviousMonthSales. Next, PercentDifference. Format that as a percentage: right click, properties, format, percent, save it. We're going to call this "MonthOverPreviousMonthCleanQ" so it's a nice clean version. Now, let's change this a little bit so it's not quite so clean. I made the numbers nice and even so you can see them. But if you come in here and let's go to the order table and let's say that this was 1,050. Where's that clean query now? There we go. Now if you want to run this for a particular year, just throw a criteria in here. Bring in the year, hide it, you don't want to see it, but I want to have it ask the user to enter the year. That's a query criteria - a parameter value. I've got videos for everything. I'll put my parameter values video in the list down below too. Yes, you can also get that from a form if you want to. Now when you run this query, it's going to say "Give me the year." Enter 2021. There you go. There's all your months from 2021, SalesThisMonth, PreviousMonthSales, and the PercentDifference - the percent growth, usually. Now, what if you want to check this month against the same month last year? It's actually ridiculously easy to do. Take this MonthOverPreviousMonthQ, copy and paste it. We're going to call this "MonthOverMonthPreviousYearQ". Design view. I wanted to show you this one first because this one's harder (Month over previous month). All you have to do here is say, "Okay, I'm checking the previous year." So Y2, get rid of this complicated mess - is simply going to be Y minus 1. M2 is simply going to be M. That's it. That's the only change you have to make. Save it and run it. Here you go. January of 2019. Let me shrink this down a little bit. January 19, previous period January 18. 120, previous period is 1 of 19, you've got sales for that. Down here, you don't have any sales for February of 20, so that's zero. Once you've got all these other calculations in place, that's super easy to change that. Month over month from the previous year is just easily subtract one from the year. The hard part is getting all this other stuff in there. Want to learn more? In the extended cut for members, I will show you how to calculate the same stuff using VBA programming. It's not hard. The stuff in the queries is actually harder than the VBA code, believe it or not. I'll show you how you can do it for any two periods. As you can see in the example here, I'm comparing first quarter 21 to second quarter 21. You can type in any two periods you want: weeks, months, quarters, years, six month periods, whatever. We'll calculate the sales using the dSum like we did before, the change, and the percentage. I'll show you how to do all that. In the extended cut, Silver members and up get access to all of my extended cut videos (we're approaching 200). There's a lot of stuff. Gold members can download these databases. 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 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 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 one 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 one, level two is just one dollar. 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 one course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the goal of the tutorial in this video?A. To create forms for data entry in Access B. To compare month over month sales in Microsoft Access C. To secure an Access database using passwords D. To automate email sending from Access Q2. Which of the following functions is NOT mentioned as a prerequisite for understanding the lesson? A. Aggregate queries B. DateSerial C. NZ D. AutoNumber Q3. What is the basic formula presented for calculating month over month sales change? A. (Month one sales - Month two sales) divided by month one sales B. (Month two sales - Month one sales) divided by month one sales C. (Month two sales + Month one sales) divided by 2 D. (Month one sales - Month two sales) divided by month two sales Q4. Why is an aggregate query necessary in this lesson? A. To combine multiple databases B. To sum order totals from order details for each order C. To divide data into separate tables D. To filter out orders with zero sales Q5. What does the Year([OrderDate]) function accomplish in the queries? A. Returns the number of days in the order date B. Returns the fiscal year C. Returns the year part of the order date D. Returns the quarter of the order date Q6. How does the tutorial suggest handling sales for the previous month when the current month is January? A. Set the previous month to February of the same year B. Set the previous month to December of the previous year C. Use the same month as the current month D. Ignore calculations for January Q7. Which function is used to create a date from separate year, month, and day fields? A. DateAdd B. DateSerial C. DateDiff D. Format Q8. What technique is recommended to correctly capture the last day of a month in Access? A. Add a year to the date, then subtract a day B. Use DateDiff to count days in month C. Add a month to the first day, then subtract one day D. Assume all months have 30 days Q9. Why does the video recommend wrapping D functions like dSum or dLookUp within the NZ function? A. To improve performance B. To sort results alphabetically C. To avoid returning NULL and replace it with zero D. To automatically format the field as currency Q10. What is the purpose of aliasing the 'SumOfOrderTotal' field as 'SalesThisMonth'? A. To avoid using built-in Access keywords B. To make the query easier to understand C. To format the numbers as currency D. To sort the results in descending order Q11. In handling percent difference calculations, what does the tutorial do to avoid a "divide by zero" error? A. Skip the calculation for months with zero sales B. Use IF to return zero if PreviousMonthSales is zero C. Replace zero sales with one D. Use a custom error handler in VBA Q12. What is a likely reason for getting an "Enter Parameter Value" prompt in your query, according to the video? A. The database is corrupt B. There is a misspelling in a field or parameter name C. The query is too large D. The report is not linked properly Q13. Which function is used to display the month name in the "CurrentMonth" query column? A. Month() B. MonthName() C. FormatMonth() D. GetMonthName() Q14. When comparing the same month in different years (e.g., January 2022 vs January 2021), how is the previous period determined in the modified query? A. Previous year is Y-1 and previous month is always 12 B. Previous year is Y-1, previous month is the same as current month C. Previous year and previous month are both Y-1, M-1 D. Previous month is always January Q15. What does the tutorial suggest for making queries look "cleaner" or more presentable for reporting? A. Add more columns to display details B. Use another query to format and present only necessary fields C. Only show order IDs D. Export data to Excel before presenting Q16. How can you apply a filter to only view sales results for a particular year? A. By setting a table property B. By adding a criteria/parameter to the year field in the query C. By creating a new form D. By adding a macro Q17. According to the video, when is it acceptable to use multiple queries chained together? A. Never, you should use only one query B. Only when you are importing data C. When calculations are complex and breaking them into steps makes results easier to achieve D. When you do not know SQL Q18. When using BETWEEN to select a date range, what issue does the instructor highlight? A. BETWEEN is slower than using >= and <= B. BETWEEN can miss records with times included on the boundary dates C. BETWEEN cannot be used in aggregate queries D. BETWEEN only works with integer values Q19. Which of the following is NOT a benefit mentioned for becoming a member on the AccessLearningZone.com channel? A. Free monthly database maintenance B. Access to extended-cut videos C. Download access to sample databases and code vault (with Gold membership) D. Access to full beginner and some expert courses (with Platinum membership) Q20. What is the best first step if you do not understand one of the prerequisite concepts covered in the video? A. Skip that part and continue watching B. Pause the video, watch the prerequisite video, then come back C. Hire a consultant D. Delete your database and start over Answers: 1-B; 2-D; 3-B; 4-B; 5-C; 6-B; 7-B; 8-C; 9-C; 10-B; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B; 17-C; 18-B; 19-A; 20-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 how to compare month-over-month sales in Microsoft Access. In this lesson, I will explain how to analyze your sales data by comparing both the current month to the previous month, as well as the current month to the same month in the previous year.This question was brought up by Jeremy, who needed to know how to perform these comparisons within Access. To accomplish this, you will need to be familiar with several concepts: aggregate queries, calculated fields in queries, the if function, DateSerial, DateAdd, dSum, and NZ. I have free videos available for each of these topics, so if any of them are unfamiliar, I recommend watching those resources first before proceeding. The core calculation for determining month-over-month sales growth is straightforward: take the sales for the current period, subtract the sales from the previous period, and divide by the previous period's sales. For example, if you have sales of $100 in February and $50 in January, ($100 - $50) divided by $50 equals 1, or 100 percent growth when formatted as a percentage. The challenge in Access is determining how to retrieve the correct sales totals for the periods you are comparing, and that's where the dSum function proves invaluable. In my demonstration, I'm using the TechHelp free template database, but any similar structure will do. Orders are tracked in two tables: one for the orders themselves and another for order details. The order total is a sum of the line items, requiring an aggregate query to combine everything. To keep things simple for this lesson, I'm working directly in the order table by adding an 'OrderTotal' field and populating it with sample data. This isn't the best method for a full production environment, but it allows us to focus on the topic at hand without getting bogged down in data entry. With a table of orders showing order date and order total, the next step is to create an aggregate query that groups the data by year and month. Use the Year and Month functions to split the order date into separate columns. Once you've grouped orders by these values, aggregate the order totals for each unique year-month combination. Now you have a summary showing, for example, total sales for each month. To start comparing months, you may want to break down the process into multiple queries. While it can be tempting to try to cram everything into a single query, dealing with aggregates often requires multiple steps. Start by creating a query that shows, for each year and month, the total sales for that period. Alias this new summed field as 'SalesThisMonth' to keep things organized. The next step is to identify the previous period you're comparing against. When comparing to the previous month, you need logic to handle when the month equals January — in that case, roll back to December of the previous year. Define the previous year's value as 'Y2' and the previous month's as 'M2' using the immediate if statement: when M equals 1, Y2 is Y minus 1 and M2 is 12; otherwise, use Y and M minus 1. Once you have these values for the previous period, you can use the DateSerial function to get the beginning date for that month (DateSerial(Y2, M2, 1)). To find the end date, add one month to the beginning date using DateAdd and subtract one day, which gives you the last day of the month. This way, you have a clear date range for the previous period. At this point, you are ready to use the dSum function to total the sales for the previous month. The domain sum will sum up all the order totals in the order table where the order date falls between your calculated begin and end dates. Make sure to wrap your dSum calls inside NZ to handle any null values, ensuring you get zero rather than an error if there are no sales for a particular period. If you want to format the result as currency, use the FormatCurrency function. With the previous month's sales in hand, you can calculate the difference between current and previous month sales. To express this as a percentage, divide the difference by the previous month's sales. Be careful to handle cases where the previous month's sales are zero to avoid divide by zero errors — include logic to return zero in such cases. For formatting, you may need to use the Format function to display percentages correctly. Sometimes when running these queries, you might encounter an "Enter Parameter Value" prompt, which almost always means a field name has been mistyped. Double check your spelling, especially when referencing fields from your query. Once you have your calculations working, you can build a "clean" query to display just the relevant fields: year, month, sales this month, previous month sales, and percent difference. For improved readability, you might display the month name using the MonthName function and abbreviate it with the Left function if necessary. You can filter the data to a specific year by adding a query parameter. Comparing the current month to the same month last year works similarly. Duplicate your previous query, but instead of the more complex year and month logic, simply set Y2 to Y minus 1 and M2 to M. This single modification allows you to compare January 2021 to January 2020, for example. Once you've set up the base calculations, switching between these two types of comparisons is a matter of adjusting the year and month values. For those interested in going beyond what we've covered here, in today's Extended Cut, I'll show you how to perform these same calculations with VBA programming. This approach will allow you to compare any two periods you want, whether that's weeks, months, quarters, or years. We'll use dSum in VBA to pull sales and compute both the change and the percentage, allowing for flexible period comparisons. Members at the Silver level and above get access to all of my extended cut videos, while Gold members can download all the databases seen in these TechHelp sessions as well as access my code vault with additional functions and examples. Platinum members get everything including my full courses, not just for Access, but also covering Word, Excel, Visual Basic, ASP, and much more. I appreciate your support and want you to know that these free TechHelp videos will continue as long as there's demand. I encourage you to like, comment, and subscribe to my channel for updates. You can also join my mailing list if you want email notifications about new videos, since YouTube no longer sends these by email. If you are new to Access, be sure to watch my free beginner-level course which covers all the fundamentals of building databases. It's available on both my YouTube channel and my website, and Level Two is just one dollar. Members of my channel at any level get access to these as well. If you have a question you'd like to see answered in a future video, you can submit it on my TechHelp page. For a full, step-by-step video tutorial covering everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListComparing month over month sales in AccessSetting up order and order detail tables Creating an aggregate query for order totals Calculating the extended price in order details Building an aggregate query to summarize monthly sales Splitting order dates into year and month fields in queries Grouping sales data by year and month Creating a summary query for order totals by month Calculating previous period's year and month with conditional logic Using DateSerial to get the first day of a month Calculating period end dates with DateAdd and subtraction Using dSum to sum sales within a date range Wrapping dSum with NZ to handle null values Calculating the difference and percent change in sales Handling divide by zero errors in percent calculations Formatting output fields as currency and percent in queries Using parameter queries to filter by year Generating clean output with formatted month names Adapting queries to compare current month to same month last year Adjusting logic to find previous year sales for the same month |
||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access month to month, month over month, year over year, quarter over quarter, sales comparisons, month function, current month vs previous month, growth, month-over-month, date period comparisons, year-over-year, monthly percentage growth PermaLink Month-Over-Month in Microsoft Access |
