Join Adam Wilbert for an in-depth discussion in this video Obtaining summary statistics, part of Office 365: Access Essential Training.
- Most queries return a record set which is a temporary table of all of the records that match your chosen criteria. Once you've gathered your records together, you can tell Access to provide some summary statistics about the records that's gathered. This is called a totals query. Let's see how we can use one to gain some insight into the performance of the Landon Hotel from month to month. I've already started a query that we can begin with called the MonthlyRevenueSummary. Let's go ahead and find it in the navigation pane and right click on it and choose to open it into Design View. Here we can see we have two different tables assigned, we have the RoomAssignments and the RoomRates tables.
From the RoomAssignments table, I've pulled down the CheckInDate column here. I also have two calculated columns, one is getting the year and the month. If you're curious on how these are working, just right click on it and say "Zoom." That'll bring it into a new Zoom box. We can change the font if we need to make it bigger. In this case, we've got the Year function, it's got a name of the column as gear here. Then we're taking the Year function and applying it to the CheckInDate data. The Year function is a built-in function inside of Access and basically all it's doing is extracting the year from a given date.
Let's go ahead and say "OK" to close that down. We also have a Month function that's doing the same thing, it's extracting the month from a given date. If we run our query right now, we'll just see the results that we have and if I look on the very bottom, I'll see that I have a total of 2,249 records being returned. These are the 2,249 unique check-ins of each of our guests here at the hotel. You can see that I have the dates here. This is the 5/13/2016 date for this particular check in and the Year function is extracting the 2016 and the Month function is extracting just the 5.
Let's swift back into Design View. I want to make one small change here. Up here on the Design tab of the ribbon, in the Show/Hide group, we have a button called Totals and it has the sigma icon right here. Let's go ahead and press that and it'll make a small change down here to our grid, you'll notice that I have this new Totals row and all of our columns have been listed as Group By. With that change being made, let's go up and press the Run button to see what the results are. Now, you can see we have a total of 714 records being returned instead of 2,249.
Essentially, what we've done is we've grouped all of the values that were identical together. So I'm just returning a single row for January 1st, 2015 and a single row for January 2nd, 2015 regardless of how many check-ins happened on each of those days. The number 714 makes sense if we consider that this database covers a two-year period, that would be 730 days total so here and there, there must have been a few days where there weren't any check-ins here at the Landon Hotel. Let's go ahead and switch back into Design View and I'm going to make a change here, I'm going to take this CheckInDate column and I'm going to get rid of it.
In order to do that just click anywhere in the column, it doesn't matter which cell you're in, and up on the ribbon, we'll press the Delete Columns button here to remove it. That'll leave us with the Year and Month functions. If I run this, we'll see that we have even fewer records, now I'm down to a total of 24. Now, we're just seeing the unique combinations of years and months that are present in the data set and all of the other records are kind of grouped underneath that. So I have a single line for the month of January, 2015 and a single line for the month of February, 2015. Again, this covers a two-year span so we see January through December of 2015 and January through December of 2016.
Let's go back into Design View. Conceptually, you can think of the Totals row as combining records into groups. Even though we're only seeing 24 lines right now, the original set of 2,249 records are still in the data set, they're just essentially stacked into these groups. Since all of the records are still there just hiding in the group, we can perform some summary calculations across the groups. In order to do that, I want to add in another column into our query grid. I'm going to find the RoomAssignments table and scroll down and double click on the AssignmentID.
Now, instead of grouping by all of the unique AssignmentID values, which would be one unique value for each of our assignments, what I can do is come over here to the Group by line and open up this drop-down list. Inside, I have a bunch of summary calculations that I can apply to all of the records that appear within the group that we've already established. In this case, I want to count up how many assignments happened within the Month and Year group so I'm going to choose Count from the drop-down list. We can also get a total revenue for the month by bringing in the currency values that are in the Rate field. So in the RoomRates table, I'll double click on Rate, that'll bring it down.
Once again, instead of grouping by each unique value that happens in the Rate table, I'm going to use the drop-down list here and I can either choose Avg or Min or Max. In this case, I want to add them all up so I'll choose the Sum option. Now, what we've got is a situation where we're taking all of our room assignments or grouping them together based off of the year and month that they occurred in then we're going to count up how many assignments there were in those groups and we're going to add up the rate values for each of those months as well. Let's go ahead and run this query here. Now, you can see if I double click here, we've got the count of assignments here and I'll double click here to expand that column.
You can start to see how many room assignments were happening in each of our months. Just like with a regular table, we can sort based off of these columns. I can use this menu over here to the top right of the header and we could choose to sort it smallest to largest or largest to smallest. Now, we can see that the month with the most check-ins was October of 2015 and the month with the least check-ins was June of 2016. Right above the last one is the February totals for both 2015 and 2016 so I can see that February is a fairly slow month for both years here.
We could also sort based off of the rate values here, so I can sort it largest to smallest. Again, you can see that February, 2016 moves up in the listing a little bit but not very far, it's still towards the bottom of the list. One other thing that we could do to our table here is come up here to the ribbon on the Home tab. You'll notice that we also have a Totals button. Now, this Totals button acts a little bit differently than the one in the query designer. If I turn it on, we'll get this new row at the very bottom down here and if I click in here, we can see a drop-down list. The drop-down is on the left-hand side instead of the right-hand side, for some reason.
But we have a drop-down list where we can choose the same sort of summary calculations. I can add up or sum the totals that are in our CountOfAssignmentID and that (mumbles) us that we have the original 2,249 records, all represented here, split up between the different months. I can also come over here to this one and choose Sum as well and we'll get a total revenue for the entire two-year period as $315,000. Let's go ahead and save this query. I'll either press ctrl + s on my keyboard or press the Save icon here on the quick access toolbar and that'll save it over the MonthlyRevenueSummary query that I've named earlier.
So with the totals query, I could quickly see that February seems to be a slow month year over year. From a management perspective, this is pretty valuable insight, maybe we can create a special Valentine's Day promotion to help bring people to the hotel. Turning on the Totals row in Design View unlocks several ways that we collect many records into a group such as our RoomAssignments here and then display the summary statistics across all of the records in that group.
The course also shows you how to build queries and action queries, create and design forms, use macros, integrate Access with the rest of the Office 365 suite, and maintain your databases over time.
- Creating a new database
- Creating tables and new data types
- Importing and entering data
- Setting up relationships and primary keys
- Adding validation rules
- Sorting and filtering table data
- Building queries
- Designing forms
- Creating reports
- Attaching macros to buttons and tables
- Working with Excel and Outlook data
- Maintaining an Access database