|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Missing Months By Richard Rost 
5 years ago
Show All Months in Query Even if Missing Sales In this video, I'm going to show you how to take a year end sales query and show data for every month of the year. If a particular month had no sales, it will show a zero instead of being missing. Zul from YouTube (a Silver Member) asks: I just finished watching your Year End Sales Report video that shows how to make an aggregate query to total sales by month. I have some months that don't have any sales at all, and the query doesn't show them. How can I show ALL of the months and just put a ZERO where there are no sales? MembersMembers will learn how to drill down even more and show sales by day. We'll use a different technique. Instead of making a table with all of the days of the month (which would be tough because different months have different numbers of days) we're going to use a Recordset to loop through the days of the specified month and fill in a temporary table with all of the needed data for that period.
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, sales report missing months, missing days, show all months, all periods, recordset, temporary table, make table query, month name, month abbreviation, monthname function, format function, currency
IntroIn this video, I will show you how to create a month-end aggregate query in Microsoft Access that displays all months in a sales report, even if there are months with no sales. We will cover how to build a months table, use outer joins, handle null values with the NZ function, format your sales values as currency, and display month names in your reports. This is a great way to ensure your sales reports always show every month, filling in zeros where needed, for a complete and accurate overview.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 create a month-end query that will show all months for a sales report in Microsoft Access even if that month has no sales. Normally, when you do an aggregate query to join and sum up records by month, for example, if that month doesn't exist, it doesn't show up in the query. So I'm going to show you how to get around that. Today's question comes from Zool, one of my YouTube subscribers, a silver member. Zool says I just finished watching your year-end sales report video that shows how to make an aggregate query to total sales by month. I have some months that don't have any sales at all and the query doesn't show them. How can I show all of the months and just put a zero where there are no sales? This is a very good question and I'm going to show you exactly how to do it. First, for the rest of you, if you have not yet watched the year-end sales video, go watch it now. Otherwise, you might not know what I'm doing. And while you're at it, go watch these other videos on aggregate queries and outer joins. They will be very helpful for what I'm about to do. Go ahead, go watch them right now. They're all free. Here I am in the TechHelp free template. You can grab a copy of this from my website, and if you watched the other videos, you know exactly where and how to get it. I'll put a link down below. Now in my order table, I've got three orders in here. I have two from January and one from March of 2021. To make a query showing the totals, I have to join that together with the order detail query. The order detail query has my extended price in it for each line. This is what we did in the other video. Let's go and create that again right here. Create query design. Let's turn off the property sheet. I'm going to take the tables, take the order table, and bring in the order detail query. There we go. Now if I bring in the order date and, let's say, the extended price, I can turn this into an aggregate query by turning totals on over here. Group by order date and then sum up the extended price. If I run this, there we go. This one from March doesn't have a value in there. Let's save this. Let's call this my orders by month query and let's add some more data. Let's go to the order form. There's one from me. There's one from Jim Kirk. Here's the one on 3/14. There's no number in here. Let's put some numbers in here. Let's add one more order. Let's put one in from today, from Will Ryker. He got some stuff and 50 bucks. Now there are four orders. We're going to ignore whether they're paid or not. We're just going to run the numbers for all of them. Now if I run my orders by month, there we go. I don't want this by individual dates because I have two orders on the same date, so I want this by the entire month. If that second order was from, let's say, 1/14, and another on 1/15, when I run that query now, I don't want two separate entries for January. I want to show just year and month. Back in the design view, let's get rid of order date. Let me shrink this up a little bit here so we can see everything. Now we're going to use the Year function to get the order year: OrderYear: Year([OrderDate]) That takes the year of the order date and puts it into a field called OrderYear. If I run it now, there you go, 2021. I don't want the whole year grouped together, so I'm going to make another one for the month: OrderMonth: Month([OrderDate]) Group by that one and then run it. Every grouping level that we add adds another row to our query. I like to put the sum at the end. So we have the year, the month, and the extended price. This was all covered in the previous video, but I wanted to get us back to this point. Now, I want to make a report and I want to see every month represented here. February, April, June, they're missing sales, but I want to see a zero. That's why I want you to watch that outer joins video. What we're going to do is make another table that's simply got 12 records in it, one through 12, with the month names if you want them. We're going to outer join that to this table. It will show a record for each of the 12 months and then a related sales record from this query. Save changes. Let's create table design. Let's make a MonthNumber field. Don't just call it "Month" - that's a reserved word; it's a function. MonthNumber. Now, this is one of those rare circumstances where I say it's okay to not use an AutoNumber. Normally, I am a preacher for AutoNumbers. I love them. You should have them in almost every table. This is one of those exceptions. It's a small table, only 12 records, never more or less, always the exact 12 records. I'm going to maintain this list of numbers myself. It doesn't need to be an AutoNumber. If I accidentally delete one like May, I can just type it back in. This is just going to be a number. I'm going to hit the primary key button and make that my primary key. No need for an AutoNumber in this table. Save this as MonthT and let's throw some data in it: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. That's it. What about the names? Don't worry about that. We'll do that next. We'll use functions for that. Save it. I know it seems silly making a table with just 12 blank entries like this, but you'll see this makes it really easy. There's another method involving programming which I'll cover in the extended cut. This is the simple way. Now let's create another query and join that month table with our order query. Orders by Month query, and we'll join the MonthNumber with the OrderMonth. Make that join manually. Bring down MonthNumber and the sum of ExtendedPrice. Run it. Now, that's why you need to learn about outer joins. Back out, change the join type to number two: Show all records from MonthT and related records from OrdersByMonth where the join fields are equal. When you run it, you see some records have blanks because there's no matching record on the other side. That's what we want. Now, I don't want to see blanks; I want to see zeros. For the sum of ExtendedPrice, let's call this field Sales. Zoom in (Shift+F2) for clarity: Sales: NZ(Sum([ExtendedPrice]),0) That converts null values to zero. Now run it. There are your zeros. Let's format this as a currency. You can right-click and go to Properties and change Format, but I don't like using that - it's not reliable. So I like to use the Format function in my queries. The field property is okay in reports and forms, but I like to manually format in queries: Sales: Format(NZ(Sum([ExtendedPrice]),0),"Currency") Now run it. There are your zeros formatted as currency. Now, let's display the name of the month. Let's call this column MonthName and use the MonthName function with MonthNumber: MonthName: MonthName([MonthNumber]) If you want to abbreviate the month, add a True as a second parameter: MonthName: MonthName([MonthNumber],True) Move this next to the other fields and run it. There you go. As the query is written right now, it's going to give us every month for every year. We need to break it down a bit more. Let's add OrderYear. You can filter however you want, for example, to use the current year. If you want to use the current year, put this in the criteria for OrderYear: Year(Date()) Be careful when you do that. If you do not include the outer join condition or account for nulls, some months will not appear. To show only months with zero sales as well, use: Year([OrderDate]) = Year(Date()) OR Year([OrderDate]) Is Null We're going to hide this field anyway since you'll probably put it in a report. If you want to have the user input the year, use a parameter in the criteria: [Enter Year] When you run it, Access will prompt you for a year. Now save this as SalesForCurrentYearByMonthQuery. Now you can just open it up from there. Zool, I hope that answers your question about how to do that. Now, what if you want even more detail? What if you want to see records by day instead of by month, to show sales by day for every day of the month? It's much harder to do with the technique I just showed you. Months are easy - every year has 12 months. But days are different; some have 31, some 30, some 29, some 28. It's better to use a different technique. In the extended cut for members, I'm going to show you how to do sales by day. We're going to use a recordset loop and VBA programming. We'll loop through all the days in the selected month. You type in the year and the month. It will create a temporary table with each day of that month and then calculate the sales for each day and fill it into the table. Then you have a full table with all the data you need, which you can use in reports and forms. The next time you run it, it creates a fresh table again. That's covered in the extended cut for members. Silver members and up get access to all of my extended cut videos. If you like the material covered in today's lesson, check out my Expert lessons on my website. I have 32 different levels of Expert classes that cover all sorts of things expert users want in Microsoft Access: different functions and much more. How do you become a member? Click the Join button below the video. After you click Join, you'll see a list of all the membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video chat sessions, and more. Gold members get access to a download folder containing all the sample databases I've built in my TechHelp videos, plus my Code Vault where I keep a lot 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 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 much more. YouTube no longer sends out email notifications when new videos are posted. If you would 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 my YouTube channel. If you like Level 1, Level 2 is just one dollar. That'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 problem does this video aim to solve in a monthly sales report query?A. Showing only months with sales and hiding months with no sales B. Showing day-by-day sales comparisons C. Showing all months, even those with no sales, and displaying zero for those months D. Showing yearly totals only, without monthly breakdown Q2. When performing a typical aggregate (SUM) query by month in Access, what happens to months with no sales? A. They show up with zero B. They are not displayed at all C. They are displayed with a warning D. They are highlighted in red Q3. What does Richard recommend as a solution for displaying all months, even those with no sales? A. Filtering out all zero results B. Using a separate table with one record for each month and performing an outer join C. Editing the order table to include null sales records D. Manually entering zero values for missing months Q4. Why is AutoNumber not recommended for the MonthT table in this scenario? A. Because AutoNumbers are not allowed in Access B. Because the table is temporary C. Because the table will always have exactly twelve records and is easy to maintain manually D. Because AutoNumbers cause duplicates Q5. What function is used to replace null sales totals with zero in the query? A. IIf B. IsNull C. NZ D. Replace Q6. How can the sum of sales be formatted as currency in the query? A. By setting a property in the order table B. By using the Format function in the query C. By changing the table design D. By using VBA code only Q7. Which function is used to display the month name based on the month number? A. DateName B. MonthName C. GetMonth D. TextMonth Q8. What type of join ensures that all months from the MonthT table are shown, even if there is no matching sales data? A. Inner join B. Cross join C. Outer join (specifically, left outer join) D. Self join Q9. If you want the user to be able to choose the year for the report via a prompt, what should you put in the query's criteria? A. Year(Date()) B. [Enter Year] C. Is Null D. Month(Date()) Q10. When creating a report showing sales by day for each day of a month (including days with no sales), what does Richard say is the best approach? A. Use a static 31-row table for each month B. Use an order details query only C. Use a VBA loop to generate a temporary table of days for the selected month D. Use built-in Access macros only Q11. What is the advantage of formatting fields using the Format function in a query rather than relying on property settings? A. Format function works only in queries B. Property settings sometimes don't persist or work reliably in queries C. It reduces the database file size D. It sorts the results automatically Q12. What is the main reason months with no sales are missing from a simple aggregate query? A. Because Access hides zeros automatically B. Because those months have no records in the source table C. Because the field names do not match D. Because of improper table relationships Q13. If you want to abbreviate month names (Jan, Feb, etc.) in your query, how should you adjust the MonthName function? A. Add a False parameter B. Add a "Short" parameter C. Add True as a second parameter D. Remove all parameters Q14. What should you avoid naming the field in the month table to prevent conflicts with Access functions? A. MonthNumber B. Month C. MonthID D. MonthValue Q15. If you want to filter to the current year but still see months with zero sales, what criteria should you use for OrderYear? A. Year([OrderDate]) = Year(Date()) B. Year([OrderDate]) Is Null C. Year([OrderDate]) = Year(Date()) OR Year([OrderDate]) Is Null D. No criteria needed Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-B; 8-C; 9-B; 10-C; 11-B; 12-B; 13-C; 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 focuses on how to create a month-end query in Microsoft Access that displays all months in your sales report, even if a particular month has no sales at all. If you've ever used an aggregate query to summarize and group sales by month, you might have noticed that any month without sales is skipped entirely in the results. In this lesson, I'll show you a way to ensure all 12 months appear in your report, inserting a zero in place of missing data.This question originally came from a viewer who had watched my previous tutorial on creating year-end sales reports. After following along, they noticed that months with no sales weren't shown in the totals. They wanted to see every month listed with a zero for the months without sales data. To follow along, it's helpful if you've already watched my earlier lessons on year-end reports, aggregate queries, and especially the one on outer joins. These lay the foundation for today's technique and are freely available on my website. Let's take a look at the data. In my sample database, the order table contains four sales spread across January, March, and the current date for demonstration. There are, however, months with no sales data. Normally when you run an aggregate query, Access just totals up the months present in your data, so if there are no sales in, say, February or April, those months simply don't show up in your results, which can make your reports look incomplete. To fix this, the first step is to make sure your aggregation groups sales by both year and month, not by the full order date. This means instead of grouping solely on the order date, you use the Year and Month functions to break down your data, so you see a row for each year and month combination with a sum of the sales. Once that's set up, the problem remains: how do you include months that have no sales at all? This is where the outer join comes in. You'll create a small reference table, typically called MonthT, holding the numbers 1 through 12 to represent each month of the year. It's a simple table: just a number for each month, which you will use to join against the month data in your sales query. Because this table always has the same 12 records, there's no need to use an AutoNumber for its primary key. Just use MonthNumber as your primary field. You're unlikely to ever add or remove months from a year, so it's safe to maintain this small list yourself. After creating this months table, you'll build a query that joins it to your sales-by-month summary query. But it's crucial to use an outer join here. That way, the query pulls all months from your MonthT table, and matches whatever sales data exists for each. Where there's no matching data, the result is a null, which you can then convert to zero using the NZ function. For extra polish, it's best to format your sales values as currency. I recommend doing this in the query using the Format function, which ensures your numbers always show up correctly formatted in reports and forms. If you want to show the name of the month instead of just the number, you can use the built-in MonthName function. You can also abbreviate month names by supplying True as a second argument to the function. To filter by year, you can add a criteria to your query that selects only the records from the year you want to see. This can be the current year using the Year(Date()) function, or you can prompt the user with a parameter to enter the desired year each time the report is run. With all these steps, you'll end up with a query that lists every month for a selected year, showing sales totals including zeros where appropriate—a much more complete picture for your reporting needs. For those looking to take this even further and see sales summarized by day, that's a more complex challenge, since months have different numbers of days, and not every day may have sales. In the Extended Cut for members, I explain how to handle this scenario using a VBA recordset loop to generate a temporary table with each day of the selected month, tally up sales per day, and fill in the gaps for missing dates. This approach creates a comprehensive day-by-day sales table you can easily use in your reports. If you're interested in step-by-step guidance for this and many other advanced Access techniques, you might want to check out my Expert series of lessons, available on my website. Membership levels provide access to additional content, full downloadable sample databases, code libraries, and more, depending on the level you choose. Free video tutorials like this one will continue to be available to everyone. If you found this lesson helpful, don't forget to subscribe to my channel, post a comment, or give a thumbs up. Additional resources and links can be found below the video or on my website, where you can also sign up for email notifications about new releases. Anyone new to Microsoft Access is encouraged to try my free Access Beginner Level 1 course, which covers all the basics you need to start building your own databases. If you'd like your question answered in a future tutorial, visit my TechHelp page and send me your query. 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 ListCreating a month-end aggregate sales query in AccessBuilding queries to sum sales by month and year Using the Year and Month functions in query fields Grouping query results by year and month Creating a Month table with MonthNumber as primary key Populating the Month table with values 1 through 12 Joining the Month table to the sales query Setting up an outer join to display all months Handling nulls and displaying zero sales for missing months Using the NZ function to replace nulls with zeros in queries Formatting sales totals as currency with the Format function Displaying month names using the MonthName function Filtering query results for the current year or user input year Adding parameter prompts for the year in query criteria Saving the final query for use in reports and dashboards |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access sales report zero missing months, missing days, show all months, all periods, recordset, temporary table, make table query, month name, month abbreviation, monthname function, format function, currency PermaLink Missing Months in Microsoft Access |
