Obtain summary statistics

- [Instructor] Once you've gathered a number of records with a query, you can then tell Access to provide some summary statistics about those records. 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. To do that, we'll go up and create a new query in Design view. Now, I wanna find out how many guests the hotel had for each month that we have data on. To find this out, we need to collect some raw data, and that'll come out of the RoomAssignments table. Let's go ahead and double-click on that, and then close the Show Table window. We'll need the Check-in Date column.

I'll go ahead and double-click on that to add it into our grid, and this will tell us when the check-ins occurred. I'll also grab the AssignmentId column, which is at the bottom. This will give us a unique identifier for each record. This will help us count up how many check-ins occurred on each day and month in just a moment. For this query, we're not concerned with who visited the hotel, so we don't need to pull any of the Guests table data at all. Let's go ahead and run this query, and we'll see the results as they are right now. We'll see at the bottom that we have a total of 2,249 records, and these represent all of the check-in data that we have across the database.

Let's go ahead and switch back into Design view. So now let's start summarizing that data. To do this, we'll need to group all those records together by the month that they occurred in, and then we'll count up how many records occurred in each month. To do that, I'll go over here, on the ribbon, and I'll click on this button that says Totals with the Sigma icon. When I do that, we get a new row down in our Query Design Editor called Total and both of our columns got the default option of Group By. In this arrangement, any record that has the same value for both the date and the assignment ID, will get grouped together into a single row.

Now, since our assignment IDs are unique for each record, if we run this query now, we shouldn't notice any changes to the record set. And indeed, I still see 2,249 records. Let's go back into Design view. However, if we change the assignment ID from Group By to one of the other options that we have in this dropdown menu here, I'll go ahead and choose Count, we'll start to see a difference. If I run the query now, we'll see that we only have a total of 714 records. Basically, what this is saying is that we have 714 unique days that had at least one guest stay at the hotel.

This database covers about two years' worth of data, and here and there, I'm sure there are a few days without any guests checked into our small boutique hotel. So this number sounds about right. In this second column, here, we have CountOfAssignmentIDs, we can see exactly how many guests stayed at the hotel for each day. So for instance, on January 18th of 2018, we had two guests. On the next day, we had a total of four. Now, this isn't terribly useful information right now, but so far, this is exactly what we've asked for. Let's make this more informative by grouping our individual days into months and years.

Let's go ahead and switch back into Design view. To do this, we'll turn to a couple of built-in functions that'll help us manage our date data. I'm gonna come to the third column, and I'll right-click in the blank cell at the very top, and I'll invoke the builder. This will get us into the Expression Builder. The top area of the Expression Builder is where our calculation, or expression, will be written. The bottom-left window displays a catalog of some built-in functions that we can make use of. Let's expand the folder here, for the functions, and then I'll go into our built-in functions. There's a group of functions here, called Date and Time, and it's out of the Date and Time group, we can find all of the date and time functions.

I'm gonna come down to the very bottom, and I'm gonna find the one called Year, and double-click on it, and that'll apply it up here into our expression area. If I take a look at the very bottom, it tells us what the year function is going to do for us. It's going to return a whole number that represents the year and all we need to do is give it a date. This date placeholder here is where we're going to feed in our date data and we're gonna go ahead and come down here and collapse the Functions folder and expand the folder that represents our Landon Hotel database that we're working with. We can find the date data inside of our tables group. Inside of the RoomAssignments table and then I'll double-click on Check-in Date making sure that I have highlighted this date placeholder right up here.

When I double-click on Check-in Date, it replaces that placeholder and it fills in the rest of my function at the top. So we're gonna take the year function, we're gonna apply it to our check-in date data, which is basically just going to extract the year from our date. I'll press OK, and that formula gets added down here in the third column. Let me double-click on this line here to expand it open. The Expression Builder gave us a default column name of expr1, then we have this colon here. I'm gonna highlight everything before the colon and give this a better column name, I'll just call this Year. There's another function that we could use called Month, it works exactly the same way.

Instead of going through the Expression Biulder, let's just write that one out manually. I'll right-click in the cell and open up the Zoom window instead. In the Zoom window, I'll change our font size a little bit so we can see what I'm typing here. And then we'll come in and we'll type the column name is gonna be Month, then I'll type the colon here, the function itself is also called Month. And the month function we're going to apply to our check-in data, which can be found in the RoomAssignments table. I'll type in an open square bracket, and roomassignments, (typing on keyboard) close the bracket.

We have a separator that's the exclamation mark, and you can see that syntax right down here. So you have the exclamation mark, and then inside of the RoomAssignments table, we have a field called CheckInDate, and I'll finish that with a closing square bracket. And then we'll finish our formula with a closing parentheses. I'll say OK to that, and that gets populated down here into my fourth column. I'll double-click on this line, just so I can expand it open a little bit, so we can see the entire text down there. Finally, I'm gonna come to the beginning, and I'm gonna delete the column that has our original check-in dates. These are the entire, or the full dates, straight out of our data set.

To do that just click at the very top of the column, that'll highlight the entire column, and then you can press Delete on your keyboard. So this is our finished query, we have the assignment IDs and we're gonna count up all of our assignment IDs, and we're gonna do that within a couple of groups. We're gonna group all of our records by year first, and then within those year groups, we're gonna group them together by month. Let's go ahead and run our query to see the results. And here, we can see that we have these columns here, represent the year, 2018. We have January at the top through December. And then we have records for 2019, January through December here.

And then we have a couple of days in January of 2020. In the left-most column, we can get a count of how many guests we had in each month of each year. So conceptually, you can think of the Total row as combining records into groups. So even though we're only seeing a total of 25 lines right now in our result set, the original set of 2,249 records are still in the data, they're just essentially being stacked up into these buckets. Since all of those records are still there, just hiding in the group, then we can perform some summary calculations here on this screen across each of these 25 groups.

To to do on the Home ribbon, I'm gonna come up and I'm gonna press this Totals button, and that'll add in a new line here at the bottom of our table. Where it says Total, I can go ahead and click, and using the dropdown menu, I can choose a summary option. I'm going to sum up all of the values that appear in this column, and we can see again, that we're getting back to our 2,249 original records. I'm gonna go ahead and save this query now. I'll go ahead and press Control + S on the keyboard and call this MonthlySummary. And that'll store it safe and sound, over here in our Navigation pane.

So turning on the Totals row in the Query Design view unlocks several ways that I can collect many records into a group, such as we did with the room assignments here, and then display that summary statistics across all of the records in the group. We chose to count up the number of records in each group, but you can also get a running total by summing the values across the group, get an average value in the group, and more. It just depends on the data that you're putting in, and the insights that you're looking to get out.