|
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
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. |
||||||||||||||||
|
| |||
| 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 |
