Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Discover how to manage data entry and reporting tasks more efficiently using Access 2010. Author Adam Wilbert presents lessons on designing forms, organizing and displaying data with form controls, creating flexible queries, and building a form-based navigation system. The course also shows how to build reports from wizards and queries, highlight important data with conditional formatting, and automate reporting processes with macros.
The structure that Access uses to assemble the reports on the page is kind of like building a layer cake or maybe even designing blueprints for a skyscraper. There are multiple sections that can design one time each, but then Access uses that blueprint over and over again as it needs them for each section of the report. Let's take a look at how this works. Now, we're back in the Two Trees database here and I've got a query set up here called OrdersByRegion. Let me double-click on that so we can see the data. The OrdersByRegion query is giving me a total count of all the orders that appeared in each region for each year. So, I can scroll through the list, I've got 2005 and 2006 and 07 and so on.
For each Region and Division I've got a count of the number of products that came from there. Okay, let's build a report off of this data. I'll close the query. In my Create tab I'll click on Report, and Access generates a basic report. Let's switch into Print Preview mode to take a look at the full sheet. We'll go to View>Print Preview. Now, I can scroll through this report and I can see that there are multiple sections that get repeated. At the very top of the page I've got what's called the report header. The report header appears once at the very top of the very first page. After the report header, I've got the page header.
This page header includes Year, RegionName, DivisionName and CountOfOrderID fields. These are the header names that appear at the top of the columns of data. The page header appears at the top of every single page. After the page header, we have Details. Each detail section is one record from our database. So, 2005>Midwest>East North Central and 49 represents one record from our query, and this is one instance of the Detail section. The Detail section repeats over and over again for every record that we're reporting. Let me scroll down to the bottom. When we get to the bottom of the page, we have what's called the page footer.
The page footer appears at the bottom of every page and this page footer is showing me what page number of the report I'm on. If I click the Next button here to go the Next Page and then scroll back up to the top, we'll see another instance of the page header, because we're on a new page, more details and scrolling down at the bottom here, when I run out of details or when I run out of records to report, I have what's called the report footer. In this particular report footer, it's cut off a little bit, but it's giving me a calculation to show me how many rows I'm having in my report. And on the very bottom of the page, I've got another instance of the page footer.
So again, it's saying I'm on Page number 2. Let's go ahead and take a look at what this report looks like in Design View. I'll Close Print Preview; it's going to take me back to Layout view. So, I'll go to the View menu here and switch to Design View. In Design View the report looks much different than what you might have expected. Each section is repeated only one time here, I only have to design it once. So, I've got my report Header Section here with the icon and the title, I've got my Page Header section here with the titles of the columns of data, I've got the Detail section, this is where the data appears, I've got the Page Footer, which has the calculation for the page number and I've got the Report Footer with the calculation of the number of records I'm reporting.
When designing your reports it's important to keep in mind that you want to keep each section as narrow as possible because it's going to get repeated over and over again. For instance, if I take my Detail section here and make it a little bit taller by clicking right above this Page Footer bar and dragging down. So, I'm just going to add a little bit of white space here, but every single time I have a row of data it's going to repeat that white space. So, if I look at that small change in Print Preview mode here, you'll see that it makes a much bigger difference in my report, because it repeats so many times. Let's go back to Design View and reverse that change. I'll grab this bar and drag it back up.
So, in the last movie we talked about grouping levels. When it comes to header and footer sections, each grouping level can have its own headers and footers. We can change the grouping level here or we can change it in Layout View like we did in the last movie. The advantage of doing it in Layout View is that it moves items around for us automatically as we make any changes. So, let's take advantage of that. We'll switch our View here to Layout View and then we'll Add a group. We're going to group based off of the region. I'll click on RegionName and then we'll sort by Year. We'll Add a sort, and we'll sort by Year.
Now, let's take a look at this report in Design View again, to see the changes that were made. We'll go to View>Design View, then we'll see I have a new section here called RegionNameHeader and the RegionName box has been moved up from the Details to the RegionName. Now we'll only see a RegionName every time we get to a new region. When I change my View to Print Preview, you'll see that in action. So, I've got my Midwest region. I don't see the word Midwest repeated over and over again. I can scroll down and I've got my Midwest data. When I run out of Midwest data, I've got my Northeast data, and scrolling further, my South data.
I can scroll back up. We'll Close Print Preview and go back to Design View. Now, I can make one small change to this report that will help clarify the data. When I created this RegionName header, the region name appears every single time, but the Page Headers up here are where the titles of the columns of data appear. Those are still appearing only once on each page. I can move these down, so every time I have a new region name, I repeat the title of the column, that'll help keep your bearings as you scroll through the report. I can go ahead and click on this Year box here and in drag it down to the bottom section below, to the RegionName header.
I'll click on DivisionName, and I'll click and drag that down as well, and finally, the CountOfOrderID, and I'll drag that down. One more time in Print Preview view, I'll change my View here, and now I've got the Midwest, I've got the Year. DivisionName and CountOfOrderID columns. As I scroll down, when I get to Northeast, I've got those column headers repeated again every time I have a new region. So, I don't lose track of what the column of data means, as I scroll through the report. Now, there's one more thing that I want to change on this report and this really drives me nuts about the way Access designs its automated reports.
Every time you create one of these automatic reports, Access puts boxes around everything and it really doesn't add anything to the legibility of this report. So, let me clean that up really quick. I am going to Close Print Preview view, that will take us back to Design View. I can go to the Format tab, click the Select All button or press Ctrl+A on your keyboard. Let me go over to Shape Outline and change it to Transparent. We'll take a look at the change in Print Preview, Print Preview here, and you'll notice that this report is certainly much easier to read. So, you'll see the same header and footer options available to you when you design forms, but they're really only applicable to forms that are set up with a continuous view property turned on, like our Employee Directory.
So, headers and footers are a limited value in forms, but they would work the same way there. In reports however, headers and footers coupled with grouping and sorting levels create the primary structure within which your data will be organized.
There are currently no FAQs about Access 2010: Forms and Reports in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.