|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Group Sales By Month By Richard Rost 
6 years ago
Group Sales By Month in an Aggregate Query Learn how to display sales by month using an aggregate query in Microsoft Access. Carl from El Paso, TX (a Gold member) asks: I have all of my orders with their totals calculated in a query. How can I show the total for each month of the year? MembersI'll show you how to show the month name (January, February, etc.), filter the records to show only a single year which we will get from a parameter and then from a form, create buttons to change the year, and then learn about field name aliases.
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! LinksAggregate Queries: https://599cd.com/aggregate
IntroIn this video, I will show you how to group sales by month in Microsoft Access using an aggregate query. We'll take an order table with order dates and order totals, and I'll demonstrate how to use the Year and Month functions to summarize your sales for each month of the year. You'll learn how to set up your table, enter sample data, and design a query that calculates the total sales per month, making it easy to analyze your sales trends over time.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 group sales by month using an aggregate query so you can take your entire list of orders and the order totals and then group them together and show a sum for January, February, March, and so on. Today's question comes from Carol from El Paso, Texas, a gold member. Carol says, I have all my orders with their totals calculated in a query. How can I show the total for each month of the year? This is actually something I get asked a lot, Carol. I cover it in my aggregate queries lessons, and in Access Expert 11, I show this example specifically, but let's go through it real quick. Here's my blank database template that you can download from my website. It's free of charge. I'll put a link down below. The blank template just has some empty forms in it. Let's create a table to store our orders. So your order table would normally have an order ID, which would be your auto number. You'd probably have a customer ID in here so you can relate it back to your customers. You'll have your order date in here, which will be a date/time value. Then you may have your order total in the order table itself as a currency or you may, like I do, have an order details table that has all the line items, then you add those up. Either way, it won't matter for this example. Let's assume that you've got the order total in the order table. If not, you'll just have to make another query and add those all up. I cover that in other lessons. You'd have all the rest of your fields in here like your bill-to address, your ship-to address, all that stuff, but this is all we need for this lesson. I'll save this as my orderT (order table), primary key, yes, that'll be my order ID. Let's open it up and put some data in it. Now the customer IDs won't matter for this example. We would have a customer table and that would be related to the customers. For this, I'll just put some numbers in. So customer 1 placed an order on 1/1 and the order was for $15. Customer 2 placed an order on 3/1 for $26, and so on. Customer 1 placed another order again on 3/4 for $13, and so on. I'll just put this stuff in, put a bunch of different orders in for different customers with some different dates in here. Notice I got three from March. Actually, let's make this one here, two from July. There we go. Now, what I want to do is make another query to group these together by month and then show the total for each month. So we need an aggregate query. If you've never done an aggregate query before (a total query), I have other lessons on that. Go watch those first. Next, down below in the description below the video, go watch my aggregate query first. Then come back to this lesson understanding how aggregate queries work. Let's close this. Now, this could be another query too. If you have, like I said before, your order details in a separate table, total them up and then you'll use that query to make the aggregate query. Queries in queries in queries. Now, let's go up to Create and then Query Design. We're going to make a query based on that order table. This is going to be sales by month. The first thing you want to do is bring in the order date and then you want to bring in the order total. Now let's turn this into an aggregate query now. We want to group by order date and then order total. We're going to sum that up. Let's save this as SalesByMonthQ and let's give it a run. Now it looks just like the data in the table. What happened here? Well, we set up group by on the order date specifically, so it's going to group all of the records from this particular date together. So all of your records on 4/2, for example. If I go back to the table, if I did have two records in here, two sales from the same exact date, 3/1, let's say, notice I'll have, if it works the right way in the query, one record for $39 instead of those two. So watch this. See? There it is. It groups them together. But we don't want it by date. We want it by month. So let's go back in here, Design View. Now to group those together, we have to break this down into its components, the year and the month. Over here, let's create two calculated fields. I'm going to create OrderYear: and that's going to be the Year of OrderDate, just like that. And we'll do the same thing for month. OrderMonth is going to be the Month function of OrderDate. Let's run it now, see what we get. Look at that. It breaks it down, so there's the year, there's the month. Now let's get rid of OrderDate. I'm going to move the OrderTotal off to the right over here like that. And now run it. Now look what we got. Since OrderDate specifically isn't in the query, it doesn't group by that. It only groups by year and then groups by month where these are unique. Notice all of the threes, all the Marches are together, all of the Julys are together. That is how you can group them together by year and by month using the Year and Month functions. Again, I covered this in my Access Expert 11 class and in my DateTime Seminar. I'll put links to both of those down in the description below the video. To learn more about your monthly sales query, in the extended cut for members, I'm going to show you a couple of different things. First of all, I'll show you how to put the month name in your query. So if you don't want to see 3 and you want to see March, we can do that. Next, I'll show you how to get the year from a form. Instead of seeing all of the years here, we can put the year value right here on a menu form, for example, and that will show up in the query when we hit the Show Sales button. Then we'll make little buttons to change the year. So if you want to go back to 2019, 2018, 2017, or forward, you can do that here. It'll change the value of that button in that box. Then I'll show you how to set up a field name alias. So instead of seeing SumOfOrders here, you can see MonthName, for example. That's all in the extended cut for members. Only 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 main goal of the video tutorial?A. To show how to group sales by year using a table B. To show how to group sales by customer C. To show how to group sales by month using an aggregate query D. To show how to add new orders to a database Q2. What field must be present in the orders table to group sales by month? A. Shipping address B. Order date C. Product category D. Customer phone number Q3. What is an aggregate query primarily used for in this context? A. To sort data alphabetically B. To combine multiple tables C. To perform calculations like sum or count on grouped data D. To change data types Q4. When grouping sales by order date, what result do you get? A. Orders are grouped by customer B. Orders are grouped by year only C. Orders are grouped by individual date values D. Orders are not grouped at all Q5. Which two functions are used to extract the year and month from the order date in Access? A. LEFT and RIGHT B. YEAR and MONTH C. SUM and COUNT D. LOWER and UPPER Q6. Why would you remove the original OrderDate field from the query after creating OrderYear and OrderMonth fields? A. To allow grouping only by year and customer B. To include more details in the results C. To prevent the data from being grouped by specific dates D. To sort the results in alphabetical order Q7. If you want the results to display "March" instead of "3", what should you add to your query? A. A calculated field using the MonthName function B. A calculated field using the Sum function C. A filter for the Month field D. A total query for each month Q8. What do you need to do if the order totals are stored in a separate details table? A. Nothing special, just use the details table B. First summarize them in a separate query, then aggregate by month C. Only sum them in a report D. Add totals manually for each record Q9. What is the recommended naming convention for the order table in the video? A. ordersTbl B. ordTable C. orderT D. tblOrderData Q10. What feature allows showing only sales from a particular year using a form? A. Setting a static filter in the query designer B. Typing the year into the SQL directly C. Retrieving the year value from a form control and referencing it in the query D. Hardcoding the year field name Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-C; 7-A; 8-B; 9-C; 10-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 group sales by month using an aggregate query in Microsoft Access. I want to show you how you can take a list of your orders, together with their totals, and then group them so you can easily see a summary of sales for each month of the year, such as January, February, March, and so on.A question I often get from students is, "If I have a table with orders and calculated totals, how can I show the total for each month?" The method I use for this starts with building a proper table structure. Usually, your order table should have an auto number OrderID and a CustomerID to relate it back to your customers. You will need an OrderDate field of the Date/Time type. You may also have an OrderTotal field in this table, or, if you use an OrderDetails table with individual line items, you sum those up in a separate query, but for today's example, let's assume the OrderTotal is right in the Orders table. Once the essential structure is in place, I input some sample data. The CustomerID field is not significant for this demonstration, but it is good practice to include it to maintain proper relationships. The table includes several orders across different dates and customers, with values for OrderTotal to simulate real sales activity. To create the monthly sales summary, we use an aggregate query, also called a totals query. If you have not worked with these before, I encourage you to review my lessons on aggregate queries to get familiar with the concept. They are a key part of summarizing data in Access. After closing your table, go to Create, then Query Design, and start a new query based on your Orders table. For the purpose of grouping sales by month, add the OrderDate and OrderTotal fields. When you turn on the aggregate (Totals) function in your query, Access groups data by every unique OrderDate. This is not quite what we want; we need Access to group by month (and year, to avoid combining sales from the same month across years). To achieve this, you create calculated fields in the query grid. One field extracts the year from the OrderDate using the Year function, and a second field extracts the month using the Month function. When you add these to the query and remove the original OrderDate field from the output, Access groups by year and month instead. Your results will now display summarized sales totals for each unique year and month combination, making it easy to analyze trends over time. This approach is ideal because it organizes your data efficiently and handles cases where multiple years are present in your sales records. It is a method I use extensively and cover in detail in both my Access Expert 11 class and my DateTime Seminar. For those who want to go further, in the Extended Cut for members, I will demonstrate how to display month names (like "March" instead of "3") in your query results. I will also show you how to filter for a specific year by pulling the year value from a form, so you can have an interactive way to select which year's sales to view. We will even add buttons to let you advance the year forward or backward right from your menu form. Additionally, I will explain how you can set up custom field aliases in your query to give your columns more user-friendly names. Access to the Extended Cut, along with other exclusive content including live sessions, is available for Silver members and above. If you are interested, you can check out the membership options on my website. But remember, these regular TechHelp videos are always available for free so you can keep learning and improving your Access skills. 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 with necessary fieldsEntering sample data for orders Designing an aggregate query for sales by month Using Group By with order date in queries Identifying why Group By on full date fails to group by month Creating calculated fields for Year and Month from OrderDate Grouping sales data by year and by month Summing order totals by month and year in a query Adjusting query fields to display summarized results |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access sales by month, group by month, aggregate query, display month name, sum of sales, sum of order total, parameter, criteria from form field, aliases PermaLink Group Sales By Month in Microsoft Access |
