Organize records with group and sort

- [Instructor] Access is way of preparing material and data for the printed page is through the report object. Reports take data from tables and queries, apply as formatting and organization rules, and then bundles all of it together into documents that are ready to be sent to a printer or a PDF file. In this movie, we're going to take a look at how access organizes data into grouping and sorting levels. If you've ever used the totals row within an aggregate query, this may seem somewhat familiar as the concept is similar. Let's get away from the Red30 Tech Database for just a moment and take a look at this sample database that I built around a deck of playing cards. The draw table represents 5,000 unique records of random card pulls from a deck of cards. The very first card that I pulled was the six of Hearts. The next card that I grabbed was the four of Clubs. I did this a total of 5,000 times. Now, I have a query called draw results that summarizes all of that data. Of the five, 5,000 random draws I saw the Queen of Diamonds, 121 times and the four of Clubs, 118 times. I want to use this data and build a report on it. To do that I'll close the query, but make sure that it stays selected in the navigation panel. Then I'll come to the create tab and take a look at the reports group. Just like with building forms, the very first option will take whatever object you have selected in the navigation panel and build a basic report on it. So I'll come up here into the report section and I'll just click the button that says report. And here is our basic report. It has a single row for every row from the original query. Now this report is organizing things alphabetically by the value text column. So I have the ACE of Clubs first followed by ACE of Diamonds, ACE of Hearts, and then ACE of Spades. So while every row from the original query is in this report, it's not organized very well. The default layout with all of these records alphabetized doesn't allow for a very detailed understanding of the data. For instance, looking over this report could you tell me which card appeared most often or how did the King of Clubs rank out of all of the Club cards? This report is not organized very well at the moment and it can't help us answer some very basic questions. We can fix this by using the group and sort options to better organize the layout. Right now our report is opened up in the layout view and here in the grouping and totals group I have a button called Group and Sort. Clicking this will open up a new panel at the bottom of the screen. This gives us two options. I can either add a group or add a sort. These will allow us to organize all of our data better in the report. Let's first add a sort, that'll allow me to sort based on the values in one of the columns I'll choose to sort the data by the count of order ID. Then I can either choose to have it go from smallest to largest or from largest to smallest. Sorting it largest to smallest reveals that the Queen of Diamonds was seen the most number of times followed by the four of Clubs just like we saw previously in the query. So already this gives us a better understanding of the data when it's sorted in a predictable way, but we could take this a little bit further and group the values first before applying a sort. I'll come back down to the group and sort panel and I'll click the X in the far right hand corner to remove the sort. Then I'll put it back in the original sequence. Now we can group values to there based on common attributes. First I'll add a group. This will allow me to group my records based off of a common field. I'll choose to group them by the suit color. When I do that I get a new section up here in the report that says black and here are all of the black cards, the Spades and the Clubs I can scroll through the report. And when I run out of of black cards I'll find the section for the red cards. So here are all of my Hearts and Diamonds in a section called red. The grouping on the sort color is alphabetized. That's why we're seeing black first followed by red. We can switch that around by changing with a on top to with Z on top. Now the top of the report says red and here are the red cards and the black cards will be down below. So now that we have a group on the suit color we can add in a subgroup on the suit name. I'll add another group, choose suit name. And now I have the data broken down into its suit. I have the red cards starting with the Diamonds and here are all the Diamond cards, I'll scroll down. When I run out of Diamond cards it'll switch over to the Heart cards. Then we'll go down a little bit further. We'll find the black Clubs and after that the black Spades. Now within these groups we can add in a sort. Now I'll sort by the count of ID and will make sure it goes from largest to smallest. This shows me that the highest Diamond card was a Queen and the highest card that was a Heart was the eight. So now the report has a much more useful layout that allows us to easily find the answers to our questions about the data. I can easily find which card was the highest ranked black card or which of the Hearts was the lowest. Let's take a look at the final layout in print preview mode so I can show you one other feature of using the group and sort panel. Using the view button on the far left hand side, I'll switch into print preview mode. Now I can see what the report would look like printed on a piece of paper. I can click to zoom out take a look at the very bottom of the page here. This is where it says the black Club cards and it starts with the four. Then if I go to page number two, we'll have the rest of the black Club cards starting here at the very top with the nine. We can better organize where the page breaks occur using the group and sort panel as well. Let's close the print preview mode. Then back in the group and sort panel. I'll click on group on suit color, click the more button you'll get some additional options. And here's an option that says do not keep group together on one page. And I'll change this to keep whole all group together on one page. With that change made, I'll switch back into print preview and take a look at the report again. Now we can see the red cards starting with the Diamonds here at the top and the Hearts there. Then we'll have to switch over to page two in order to see all of the black cards. This better organizes the data so that we don't have a page break falling right in middle of the data set. So I encourage you to spend some time with this data set and play around with the different configurations of the group and sort options. You can rearrange the report in a variety of useful ways. Each one providing an alternate insight into the same data set. Understanding, grouping and sorting behavior within your reports is key to creating documents that deliver the information needed in the most organized manner possible.