Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Understanding report structure

From: Access 2010: Forms and Reports in Depth

Video: Understanding report structure

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.

Understanding report structure

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.

Show transcript

This video is part of

Image for Access 2010: Forms and Reports in Depth
Access 2010: Forms and Reports in Depth

38 video lessons · 14760 viewers

Adam Wilbert
Author

 
Expand all | Collapse all
  1. 1m 27s
    1. Welcome
      1m 2s
    2. Using the exercise files
      25s
  2. 15m 53s
    1. Introducing forms
      2m 41s
    2. Designing for the end user
      45s
    3. Exploring the database
      1m 49s
    4. Creating a form with the Form Wizard
      6m 43s
    5. Refining the form in Layout view
      3m 55s
  3. 24m 33s
    1. Organizing the form elements
      7m 14s
    2. Formatting
      4m 48s
    3. Modifying the form through its properties
      6m 56s
    4. Adding a header and some polish
      5m 35s
  4. 1h 2m
    1. Introducing form controls
      3m 48s
    2. Using lines and rectangles
      2m 48s
    3. Organizing screen space with tabs
      4m 47s
    4. Adding buttons
      5m 3s
    5. Linking to external content
      4m 15s
    6. Entering and selecting data
      5m 8s
    7. Controlling input with option groups
      6m 0s
    8. Attaching documents
      6m 49s
    9. Attaching images
      5m 8s
    10. Understanding the subform control
      4m 13s
    11. Adding charts
      7m 9s
    12. Linking controls
      7m 41s
  5. 21m 42s
    1. Creating the main menu
      8m 49s
    2. Adding a splash screen with startup options
      5m 35s
    3. Creating a customer form
      7m 18s
  6. 45m 20s
    1. Grouping and sorting data
      4m 36s
    2. Understanding report structure
      6m 12s
    3. Building reports from wizards
      5m 0s
    4. Building reports from queries
      6m 34s
    5. Formatting conditionally
      6m 59s
    6. Calculating fields
      4m 35s
    7. Adding the finishing touches
      4m 49s
    8. Populating pre-printed documents
      6m 35s
  7. 15m 8s
    1. Printing reports
      3m 6s
    2. Tweaking the design
      7m 10s
    3. Automating the workflow with macros
      4m 52s
  8. 58s
    1. Next steps
      58s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Access 2010: Forms and Reports in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.