Group and sort records

- [Instructor] Understanding the grouping and sorting options available with setting up your reports will allow you to have complete control over the organization of your data. Once again, the layout view is gonna be helpful here, since the report will be populated with live data while you make adjustments, so you can see your changes update in real time. I've created a new query here called check in analysis. It groups all of our room assignment data based off of the room type and name, and the type of bed and day of week that they occurred in. Then we have some summary calculations that'll give us the total number of check-ins for each group as well as the total revenue for each group.

I also have a column called sort day which simply gives us a numerical representation of the day of the week. This will allow us to sort our records logically. If we try to sort based off of the day of the week column, then access would do it alphabetically, with Friday first and then Monday, and obviously that's not ideal. So let's build a report off of this data so we can organize it and print it out. I'll go ahead and close the query, but make sure that it stays selected over here in the navigation pane. Then, come up to the create tab, and in the reports group we're gonna create a quick report, just by clicking on the report button.

Access drops all of the records from the query into a basic report and leaves us in layout view, where we can adjust its layout. Let's go ahead and reduce the width of each of these elements so that everything fits onto a single page. I'll just click on each one, and then come over to the right and drag it in a little bit. Now we can actually get rid of the sort day column. This doesn't need to be displayed on our report. It's only gonna be for the internal structuring of things. Let's go ahead and click on the data column, and press delete.

I'll do the same thing with the label, and then I'll click one more in this column of empty boxes and delete that as well. Then, I need to make sure to come all the way down to the very bottom of the report and adjust this page section here, that's actually spilling off onto the edge of the page as well. I'm just gonna select it and press delete. So now our report fits onto a single page. At this point, I want to start grouping my records together so that this report is easier to read. On the design tab of the ribbon, come over to the grouping and total section and press the group and sort button. This will open up a new window at the bottom of the screen where we can start adding groups and adding sorts.

Let's first add a group. That will create a new line here that says group on, select a field and I can select a field from the query. Let's group on type first. When I do that, we can see that all of our balcony rooms get grouped together, and then below the header balcony, we can see the room name, bed, day of week, and so on. If I scroll through this report, we can see that when we run out of balcony rooms, we move to the deluxe rooms. We have those here, and then we have the economy rooms down below. If I come back down to the grouping and sorting window, I can click on this bar and press the more button, we have some options here. We can either sort with A on top or with Z on top, we can sort by the entire value, or by the first character or the first couple of characters.

We can add some totals, we can add a header section, or a footer section if we like, or we can choose not to keep together on one page, or make sure that we keep the entire group with the header and footer. So those are some extra details we have within our group. Let's just go ahead and push the less button here. Below our group on type, I'm gonna add in another group, so I'll go back and press the add group button and this time we're gonna group based off of the room name. Once again that updates our report up here, so now we have the balcony type rooms and we have the Westminster rooms and inside there the different bed configurations of our Westminster rooms.

When we run out of those details it switches over here to deluxe with our Cambridge Then we have economy with the Piccadilly room and the bed configurations within there. So we have queen and two doubles and so on. Let's add in another group on bed now. Press add group and this time choose the bed category. That updates the report again, it goes through and see the different records and how that organizes the report. Finally, let's go ahead and sort these based off of the day so that they appear in the right chronological order here. We'll add a sort, and choose sort day. That'll sort everything out so that they're in the right order.

So now we can take a look at the overall daily statistics of each of our rooms, broken down by type of room, the room name, and the type of bed within that room. So this is one arrangement that we can make our data fit. Let's take a look at another. On the far right of these bars, we have the option to move each of these sections up or down or delete it entirely. I'm gonna take the sort day and I'm gonna move it all the way to the very top of the stack. It's going to click on the up arrow and then follow it up to the top. Below the sort day, I'm gonna group everything on the day of the week. It's going to click on this group bar and change it from type to day of week.

Now we can see the report updates. We have the day of the week header here, so Sunday and then the rooms are down below that. When we run out of details for Sunday, we'll get to Monday and so on. Below that, I'm gonna group on type. We'll go ahead and change this one from room name to type and then I'll delete this group here, that says group on bed, we'll come over to the far right press the x and instead, we'll add a sort by total revenue. That'll add it in from smallest to largest, let's go ahead and reverse that from largest to smallest so that we have the largest revenue room up here at the top of the stack and then the lower ones below that.

So now here's a totally different look at our data, broken out by day of the week, and then we can see the room types underneath that and the revenue contribution of each type of bed within the type of room. So in this report we can see that on Sunday, our balcony rooms have two different types, king and two double beds, but we can see that the king arrangement brings in about twice as much as our two double bed configuration. When you're done messing around with the group and sort options, we can go ahead and close that window by clicking on the x over here on the right or by simply toggling it off using the toggle button up on the ribbon. So setting up the grouping and sorting options allows you to experiment with the presentation of your data.

Adding appropriate sorting to your data will bring the most important insights to the top of the list, so they don't get overlooked.