|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Year End Sales Report By Richard Rost 
6 years ago
Year End Sales Reporting in Microsoft Access With 2020 coming to a close in a few days, this lesson will take a look at how you can generate your year-end sales reports. I'll show you how to create an aggregate query to group your sales together by year and month and total them up by month. Jaime from Henderson, Nevada (a Gold Member) asks: With 2020 coming to a close soon, I need to put together my year-end sales report. I need to create a report with just sales from 1/1/2020 to 12/31/2020, showing the total sales for each month. It would be nice to not have to edit this report every year to change the dates so my secretary can just run it next year without me being involved. Can this be done? MembersI'll show you how to create a formatted printable report showing the month names and sales totals. We will put a text box on the main menu that will have the year in it so we don't have to keep typing it in. Lots more!
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! LinksBetween Keyword: https://599cd.com/ACB3
IntroIn this video, we'll learn how to create a year-end sales report in Microsoft Access by building an aggregate query to group sales data by month for a specific year. I will show you how to set up a simple order table, enter sample data, handle date fields with time values, use the YEAR and MONTH functions in queries, and set up a parameter to allow users to enter just the year so the report can be run annually without editing. We'll also discuss why using BETWEEN for date ranges can sometimes miss records and how to avoid this issue.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.In today's video, since the year is about to end, I'm going to show you how to do year-end reporting. We're going to take all of our sales from 2020, group them together in an aggregate query, group them by month, and show the monthly sales total. This way, it's easy to generate your year-end reports. Today's question comes from Jamie from Henderson, Nevada, one of my gold members. Jamie says, with 2020 coming to a close soon, I need to put together my year-end sales report. I need to create a report with just sales from January 1, 2020 to December 31, 2020, showing the total sales for each month. It would be nice to not have to edit this report every year to change the dates so my secretary can just run it next year without me being involved. Can this be done? Of course, Jamie, Microsoft Access does this quite well. Let me show you how. OK, here's my blank customer database. You can download this right from my website. There's a link down below. It's a free template. Open that up. I've patched up the main menu a little bit since the version that was posted on the website, but it's basically the same thing. In fact, for this lesson, we really don't need any of this stuff. Let's just create an order table. All right, create. Table design. Start off with our auto number, order ID, auto number. You need an order date. That's a date/time field. You want an amount. That's a currency value. You'd want the rest of the stuff in here for the order, like the customer ID. You're going to have a second table for all your order detail line items, all that stuff. But for this example, let's just assume you've got all the information you need in the one table. I cover how to properly set up an order entry system in my regular expert lessons. And I'm going to be setting up a TechHelp video on this very soon. But for now, let's just assume this is your order table. It's all you really need for this example. Save. Call this MyOrderT. I'm going to be using my primary key, yes. And let's put some sample data in. So let's say you've got some dates in here from before 2020. So let's say you have some sales in here from 5/5/19 for $100. Maybe from 6/21/19, $150. So those shouldn't show up on the report. Now let's put in some stuff from 2020. So 1/1/20 for $50. Let's put in 2/5/20 for $75. Let's put another 2/5/20, because we want these to group together, $120. Another February, let's go to 2/18/20 for $800. And then a couple more records in here, so 4/1/20. It is currently 2020, so the year should default. All right, $15. And if you don't have all this set up in one table like this, just make a query and join it together. All right, do a total query. I have lots of videos on that stuff. Aggregate queries, they're called. In fact, we're going to do an aggregate query pretty soon here. You just group together your order table with your details, do an aggregate query, and group it together based on the order ID. That way, you get the total for each order. I'm going to show you how to group these together for each month in just a minute. All right, a couple more values in here. Let's put in 12/1/20, 12/1/20, $20. Not the year, $120. And I forgot an amount up there. I'll put in 12/31/20. Okay. Now I want to show you a problem that could exist. If you have orders in your database with dates and times, I know a lot of people use Now when they have records that are created because you want the date and time that the order was placed. Maybe you want to know exactly when the customer was in your shop. That way, you can generate a list later on to see what are your popular times of the day for orders being placed. So your data might look like this: 12/31/20 at 5:13 PM. That's too wide for that field, so we'll just widen it out. You might have a date in there like that. That's perfectly possible. In fact, let's make this one smaller. Let's make this the big order, just so you can see what happens. So here's my date. As you can see, that date sometimes includes the time. Some of these other ones might have time values in there as well, but that's not as important as 12/31 having a time on it. I'll explain why in just a minute. Next step is actually let's put in a couple of dates from 2021. Let's say there are a couple of advanced orders in the system. You don't want these showing up also. Maybe you have a couple. Or maybe let's pretend that we're running this at the end of January 2021. You're doing your year-end report for 2020. You don't want to see the couple of orders that have already been entered for this new year. Some of you will be watching this in the future. I get people all the time saying, hey, I'm watching your lesson from 2007 and it still works great. Of course it does. This stuff hasn't changed in 20 years. Well, let's save it. Now let's make a query and bring in just the stuff from 2020. So let's create query design. Let's bring in my order table. Let me close this. All right. Let's bring in all the fields, the star, just so you can see everything. Now I want to limit the order date. A lot of you know how to use the BETWEEN keyword. I've got lessons on that. You can watch those if you want to. Down here under criteria, you can say BETWEEN. Now if you're typing in actual dates, let's put in actual dates for now. 1/1/2020 and 12/31/2020. That's what you'd think would give you the proper year. Let's save this. This will be MyOrderQ. Now let's run it. Now look what happens. This is a duplicated field. We'll talk about that in a second. Notice I've got all the records from 2020 except I'm missing that 12/31 at 5 PM order. BETWEEN includes the endpoints, however, a date by itself without a time is considered that date at midnight. It doesn't include everything after it. So 12/31 at 5 PM is technically outside that range. Keep that in mind when you're working with dates. Unless the data in your tables is strictly just dates, then you have to use this next method because this won't do it. In fact, I have a whole other video that I did on the subject called "BETWEEN is Not Always Right." I'll put a link down below. I think it's called "BETWEEN is Wrong." Look for the links down below the video. The way we'd handle this particular situation is not with the BETWEEN keyword, but we'd use an inequality. We'd say the date has to be greater than or equal to 1/1/2020 and less than 1/1/2021. What that'll do is it will give you everything from 1/1/2020 at midnight and less than but not equal to 1/1/2021. That'll give you December 31st all the way up to but not including midnight of the next day. If I run this now, you get that. There's my new record that I was missing. So that's important. Keep that in mind. Now, this is what Jamie wants to try to avoid. He doesn't want to have to keep changing the query so that his end user, the secretary, can just run this report and not have to worry about changing this in 2021. You don't want to have dates hard coded all over your database and your queries and your VB code and your forms. You want the user to be able to specify it. You could put parameters down here. If you don't know what parameters are, if you have never done a parameter query before, go watch that video. I'll put a link down below. I have a video on doing parameter queries. Down here, you could put "Enter start date." And then you could put "Enter the end date" here. But we're going to do something better than that. We're not going to make the user type in two dates. Since this is an annual report, I just want to type in the year. I don't want to have to type in 1/1/2020 and then 12/31/20. Let's just type in 2020. So how do I do that? First, let's get rid of this. Let's isolate the year from the order date, just the year of whatever this is. We can do that with the YEAR function. So let's create a new calculated query field. Again, I have videos on calculated query fields. Go watch those. I'll come down here. Let's just call it OYear, that's my order year. What's that going to be? It's going to be the YEAR function of, and then in brackets, sometimes you have to put the brackets on, sometimes you don't. I like to do it in queries: [OrderDate]. If you don't have spaces in your field names, usually you don't need those brackets, but sometimes you do. Now run the query, and look at that. I've got the order year right here, isolated in its own field. Now all I have to do is put query criteria on there. Again, I have videos for query criteria. Go watch those. That's the criteria list down here. Now I can put the actual value 2020 in there if I want to, and now I get just 2020, or I can make that a parameter: [Enter the year] like that. Now whenever this runs, enter the year. Give me 2020. There you go. Run it again. Give me 2021. See that? This is powerful stuff. I covered all this and lots more in my full classes. If you wanted, let me run this again, if you wanted a report with all of the details with each order ID and the customer and all that stuff, you could take this data and throw it right into a report. Just put report grouping levels on. Again, I have other lessons on that. But since Jamie only wants a simplified year-end report showing the totals for each month, let's just do an aggregate query here. Let's group this together by the month. Let's isolate the month over here, okay? OMonth: that's going to be the month of [OrderDate]. No criteria there, we want to see them all, 1 through 12. Now run it, 2020. There's your month. Again, I have separate lessons on all these date functions like YEAR, MONTH, DAY, HOUR, MINUTE, all those. DATE SERIAL is lots of good stuff. So now we're ready to turn this into an aggregate query. What's an aggregate query? Again, go watch the aggregate query video first. But what that basically does is it groups the data together based on whatever fields you pick. So in this case, I want to group this together by year and month. All the years are grouped together, and all the months that are different are grouped together. Each one of these are different. It's like these three records will get grouped together. How does that work? First thing we have to do is get rid of this star, because you can't aggregate based on an asterisk. That's okay. We don't really need all this data anyway. So I'm going to get rid of that OrderT.*. Now in here we need the year, because we have to ask for the year, then we need the month to do the grouping. Let's turn on totals now. Notice how it says Group By and Group By. If I run that now and enter the year, look at that. They're all grouped together. We have one individual record for February, and there are three orders in there. How do I see what that order total is? Now you can add the amount back in, but you don't want to group by, because if you group by, it's going to show you each individual record because they're all unique. Unless you had two orders that were both $100; those would get grouped together. What we want to do is total those up. Total these three things up. That's where the aggregate query comes in handy, because now I change the Group By to Sum. There's Sum, Average, Min, Max, all kinds of stuff. I have whole lessons on this stuff. But Sum is the one we want for this one. Now when I run it, 2020, I get the total for each month. These should sort in the right order. If not, you can just throw a sort on here, and that should sort those in the right order. Now you have the data to put into your report. I like to do this in that query first, because now you can use this query in other places too. If you want to do a quick summary, like a form, you want to pop up a quick summary form in your database to see which are monthly sales totals of the year or so far, or you want to make a chart. You want to put a chart up, maybe a little pie chart summary, like a switchboard, so you can see where your sales are for the month. All that kind of stuff. I love these aggregate queries, they're great. Again, I have lots of other free videos on my website that explain how to do a lot of this stuff in more depth, too. Plus my full courses cover this in detail. So now Jamie, hopefully this explains enough to you so you can take this query and use this query as the basis for your report. Throwing the report together is easy. I'll show the members in the extended cut video how to put the report together. It's quite simple. Plus members, I'll show you how to do this. Every time we run this query, whether we're in here or whether we're out here and we have a report that's based on it, every time you open this up, you have to type in the year. If you're running it a bunch of times, that gets kind of tedious. I'm going to show you how to put a field right here on your main menu and a button. You'll click the button, the button will say "open up the year end report," and you can put in the year there. I'll have that year default to this year. We will cover all that in the extended cut. Want to learn more about this stuff? In the extended cut for members, 15 minutes long, I'll show you first how to put the year field on the main menu so you don't have to keep typing in 2020 every time you run this query. In fact, I'll have it default to the current year. I'll put little buttons on there for you so you can click the little buttons that go plus and minus year. That's neat. We'll make buttons to run the sales query. Then we'll build the sales report, make a button for that. In the report, I'll show you how to bring all that information in. We'll put the year total across the top so it says "Annual Sales for 2020" so that doesn't have to keep repeating. The month, the monthly sales total that we get from the query. I'll show you how to put the name of the month in there, with a little function. And then of course a grand total on the bottom. This is all covered in the members only extended cut, 15 minutes long. Silver members and up get access to all of my extended cut videos. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. And they'll always be free. QuizQ1. What is the primary purpose of the year-end reporting covered in this video?A. To show monthly sales totals for a specific year B. To display customer contact information C. To print invoices for each order D. To track product inventory levels Q2. How can you ensure that records from just 2020 are included when creating a year-end sales report? A. Filter only by amounts greater than $100 B. Use date criteria in the query for the range covering 2020 C. Only select orders from a specific customer D. Order the records alphabetically by customer name Q3. What is a potential pitfall when using the BETWEEN keyword with date fields in Access? A. BETWEEN does not include any values at all B. BETWEEN may exclude records with times later in the day on the end date C. BETWEEN cannot be used with numbers D. BETWEEN only works for text fields Q4. What method is suggested to ensure all records, including those with a time value on 12/31/2020, are included in the query? A. Use greater than or equal to 1/1/2020 and less than 1/1/2021 in the criteria B. Use greater than 1/1/2021 only C. Use less than 12/31/2020 D. Use no criteria at all Q5. What is an advantage of using a parameter query for year selection according to the video? A. Allows user to enter the year rather than hard-coding dates each time B. Forces the user to enter every order detail C. Prevents the use of aggregate functions D. Disables other query criteria Q6. How can you isolate just the year from a date field in an Access query? A. By using the YEAR function B. By using the MONTH function C. By dividing the date by 12 D. By using the SUM function Q7. What calculated field name is used for the year in the query shown in the video? A. OYear B. DateYear C. OrderYr D. SaleYear Q8. What is the purpose of creating an aggregate query as described in the video? A. To combine and sum data based on grouped fields, like month and year B. To create new records for each customer C. To delete duplicate orders D. To generate random numbers for each sale Q9. How do you group sales by month in an Access query? A. Use the MONTH function to create a calculated field B. Filter by payment method C. Sort records alphabetically D. Select only the first record for each order Q10. Which aggregate function should be used to show the monthly sales total? A. Sum B. Min C. Count D. Average Q11. What is the benefit of creating this aggregate query first before making a report? A. It allows reuse of the totals in other parts of the database B. It deletes records after aggregation C. It prevents future sales from being entered D. It disables parameter prompts Q12. What will be shown in the report based on this query, as Jamie requested? A. Total sales for each month of the selected year B. Customer addresses only C. List of overdue invoices D. Product inventory for each month Q13. In the members extended cut, what feature is demonstrated to avoid retyping the year each time? A. A main menu field and button to pass the year parameter B. Hard-coding the year in the query C. Deleting all old records annually D. Exporting the report to Excel only Q14. What should you avoid when hard-coding date criteria in queries according to the video? A. Having fixed dates that must be changed each year manually B. Using parameter prompts C. Using calculated fields D. Using the COUNT function Q15. What is a recommended solution if your sales data is spread across multiple related tables? A. Create a query that joins the tables and aggregates at the order level B. Enter all data into a single spreadsheet C. Delete unrelated tables D. Only aggregate using Excel Answers: 1-A; 2-B; 3-B; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on creating year-end reports in Microsoft Access. As the end of the year approaches, I want to show you how you can gather your sales data for a given year, group it by month, and display monthly sales totals using an aggregate query. This approach makes it much easier to produce your annual reports without hassle.Let me give you an example scenario. Suppose you have a database with order information, and you need to create a sales report that only shows transactions from January 1, 2020 through December 31, 2020. On top of that, you want the report to display each month's total sales. Ideally, you would like a solution that does not require you to update the report manually every year, so your staff could run it annually without your intervention. Access has plenty of tools to help with this. To set this up, begin by making sure your database has a table that stores the order date and the sales amount for each transaction. For a basic example, you might include just an order ID, order date, and an amount in your table. More advanced databases could have related tables for customers and order details, but for the purpose of this lesson, we will use a simple orders table containing all necessary fields. Once the table is ready, you might want to add some sample sales records, ensuring there are entries before, during, and after 2020. Make sure to add a few records with date/time values to illustrate an important point: when you store dates with time components, Access may treat them differently in your queries. For example, if you have an order placed on December 31, 2020 at 5:13 PM, Access can misinterpret this if your query is only looking for dates at midnight. A common mistake is to use the BETWEEN keyword to specify the range, such as BETWEEN 1/1/2020 AND 12/31/2020. However, this only fetches orders up to midnight on December 31, 2020, and will miss any later that day that include a time. Instead, it is better to use a greater-than-or-equal-to comparison for the start date and a less-than comparison for the first day of the next year. This will include all sales from 2020, regardless of the specific time of the transaction on December 31. Ideally, you want your annual report to prompt the user just for the year, and not require entry of both start and end dates. To achieve this, add a calculated field in your query to extract the year from each order date using the YEAR function. Now you can let your query prompt the user to enter the desired year and filter records based on that year value. This makes generating future year-end reports much easier. Once you can filter records by year, the next step is to summarize the data by month. Add another calculated field to extract the month from the order date using the MONTH function. Then turn your query into an aggregate query, grouping by year and month and calculating the sum of the sales amount for each group. This will give you the monthly sales totals you need for your year-end report. Aggregate queries in Access allow you to group data based on selected fields and perform calculations such as sum, average, min, or max for each group. For a year-end summary, you want to group by year and month, and sum the sales amount. With this query in hand, you have a foundation for your report. You can use it to create summary reports, dashboards, or even charts that visualize your sales data over time. For those who want to take the next step, the Extended Cut for members covers additional features. In the members-only video, I show you how to build a user interface where you can enter the year on your main menu, with buttons for incrementing or decrementing the year as needed. It will default to the current year but allow easy changes. You'll also learn to create a command button to open the year-end sales report and see how to display the selected year at the top of the report. I demonstrate how to show the full month names, display subtotals for each month, and add a grand total. If you want to join as a member and access the Extended Cut as well as other exclusive content, you can find all the details on my website. 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 an order table for sales dataEntering sample order data with various dates Handling order dates with time components Filtering records to include only a specific year's sales Explaining the limitations of the BETWEEN keyword for date ranges Using inequalities for accurate date filtering Creating parameter queries for dynamic user input Extracting the year from a date using the YEAR function Applying query criteria to filter by user-supplied year Extracting the month from a date using the MONTH function Grouping query results by year and month Creating an aggregate (totals) query to sum sales by month Sorting aggregated monthly sales data for reports |
||||
|
| |||
| Keywords: TechHelp Access 2020 Year End, 2021 Reporting, Year End Report, Year Function, Between Keyword, CDate Function, Parameter Query, Group By Month, Month Function, Date Functions, Year End Sales Report, End of Year Monthly Totals, Yearly End, Month End, Ineq PermaLink Year End Sales Report in Microsoft Access |
