Report summaries are added to an Access report through the use of the Totals feature. Learn how to create a calculated field in the detail footer section of the report to summarize data per group and provide a grand total across a number of related records.
- [Narrator] So far, our CheckInAnalysis report has organized and grouped the records that were gathered by the CheckInAnalysis query. We can also add some statistical information about each group right inside of the report. This will give you some quick grand total figures that can help in understanding the data and making comparisons across days. Right now, the report is opened up in the Report View. I'm gonna come over here to the View button, and we're going to switch it over to Layout View. Now, before I get any further, though, I do want to open up the Group & Sort section, and I'm gonna remove the group that we have here on Type.
Just go ahead and select it, and then press the x in the far right-hand side. That'll give us a slightly different look to this report, but it'll give us larger areas that we can aggregate across. Now this report is organizing everything based off of the day of the week, and we can see, for instance, on Sunday, that the Manchester King Suite is bringing in the most revenue, whereas the Two Doubles version of the Piccadilly Economy room is bringing in the least. Now, what I want to do is add some aggregate totals here at the bottom of each day, so that we can get the total revenue across each day, as well as the total number of check-ins across each day.
To do that, I'll click anywhere in the TotalRevenue column, doesn't matter which cell you select. Then, on the Design tab of the ribbon, come over and press the Totals button. This will reveal a number of aggregate functions that we can apply to our data. We can sum up the values, we can find their average, count 'em up, we can count up their values, find either the min or the max, or also the standard deviation or variance. Let's go ahead and just select Sum, and that'll create a new box down here at the bottom that adds up the total dollar value, or the total revenue, across each day. Now, it's a little bit narrow right now, so let's go ahead and select that box and come to the bottom, and I'm gonna click and drag it open just a little bit more so we can see the full number.
Let's also do the same thing for our TotalCheckins. Go ahead and click anywhere in this column, then come back up to totals, and we'll click Sum again. That'll give us a total number for each day. Finally, I want to format this value right over here so it appears as a dollar value, so, we'll click on the box underneath TotalRevenue, and on the Format tab, we'll come over here to our Number formatting and I'll click the dollar sign to apply a currency format to it. Now we can go ahead and switch back into Print Preview mode to see a better look at this data. I'll go over to the Design tab and switch my view, by clicking on the bottom portion of the View button, over into Print Preview.
So here's my finished report. We can see the aggregated the aggregated values for each day here at the bottom, and I can see, for instance, that on Sunday, we have a total of 312 check-ins that bring in $51,000. On Monday, we actually have more check-ins but fewer dollars coming in, and that's probably because we charge a higher rate for a weekend room. So now in our report, for each day of the week, we can view the various room configurations and get the total revenue that each contributes. Now we can also see the overall revenue and counts that have occurred for each day across the full data set. Now we can compare how our performance at the hotel fluctuates from day to day.
By grouping the records together and calculating some summary statistics across the groups, you enhance the value of the report by providing additional aggregation information at each change in category. This allows you to more easily compare data across the group's categories, and get a bigger picture of what's going on in the organization.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.