Start learning with our library of video tutorials taught by experts. Get started

Access 2010: Forms and Reports in Depth

Building reports from queries


From:

Access 2010: Forms and Reports in Depth

with Adam Wilbert

Video: Building reports from queries

Building a report off an existing query is probably the easiest way to get a great looking report that displays some fairly complex information from your database. By leveraging the power of queries, and feeding the results into the report's record source, you'll create a good foundation from which report design becomes just a matter of dragging-and-dropping fields into the appropriate locations. I've said this before about Access, but sometimes all of the wizards and automated object-creation features of the program can really get in the way and just make things more confusing than they really are. Once you understand what's going on behind-the-scenes, it's often the case that the hand-built from the ground up really is the way to go.
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

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
Access 2010: Forms and Reports in Depth
3h 7m Intermediate Feb 14, 2012

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.

Topics include:
  • Designing for the end user
  • Organizing form elements
  • Formatting a form
  • Adding headers
  • Linking to external content
  • Entering and selecting data
  • Adding charts
  • Creating a main menu
  • Creating a customer form
  • Understanding report structure
  • Building reports from wizards and queries
  • Printing reports
Subjects:
Business Forms Databases
Software:
Access Office
Author:
Adam Wilbert

Building reports from queries

Building a report off an existing query is probably the easiest way to get a great looking report that displays some fairly complex information from your database. By leveraging the power of queries, and feeding the results into the report's record source, you'll create a good foundation from which report design becomes just a matter of dragging-and-dropping fields into the appropriate locations. I've said this before about Access, but sometimes all of the wizards and automated object-creation features of the program can really get in the way and just make things more confusing than they really are. Once you understand what's going on behind-the-scenes, it's often the case that the hand-built from the ground up really is the way to go.

In this exercise, I've gone ahead and pre-built a query that totals up our customer's lifetime spent amount with the Two Trees Olive Oil Company. Let's go ahead and open up TotalSalesCustomers query. I'll double-click on it to open it and we'll see that we're getting our first and last name of the customer, the total amount of money that they spent with the company, and the state that they are from. Let's take a look at this query in Design View. I'll close the Property Sheet over here to give myself some room. So I've got my four tables that I'm building this off of; Customers table, the Orders table, the Products table, and the States table. From those four tables, I'm pulling the first and last name, the price, and the state name.

And on Price, I've got the Totals turned on and I'm adding up the values with the Sum function here. So this is the data that we're going to be using in our report. I'll go ahead and close the query. We'll start creating a new report from scratch. I'll go to the Create Tab and inside of Report & Design view. Now, just like with forms, the first thing I want to do is connect it to a data source. I'll go ahead and go to Property Sheet, we'll look at the properties of our report, and we've got this Record Source property here. I can use the drop-down menu and choose the query TotalSalesCustomers.

At this point, we can go ahead and start setting up the sections of our report. We'll go to the Design Tab, the Group & Sort section, turn that on, and then we'll add a group. I'll click on Group; I'm going to group this by state. Go ahead and click on the StateName. That will add a StateName Header to our report up here at the top. I'll also add a sort. I'll add a sort and I'll sort the values within the states by the Lifetime Sales or the total spent amount. Let's go ahead and turn off Group & Sort to get rid of that window and I'm going to scroll down. Now, we've got that Detail section to make it a little smaller, here and I'll drag this up.

Now I can see everything on one screen. At this point, we can just start dragging-and-dropping fields and dropping them into our reports. I am going to go to Add Existing Fields. Since I've connected this to a data source, these are the four fields from that query. So I'll take FirstName, LastName, and Lifetime Sales, and drop them into the Detail section. I can just double-click on each one. I'll take StateName and I'm going to drag-and-drop it into the StateName Header, here. Now, let's go ahead and rearrange these a little bit. I'll type FirstName, and I'll drag it and I'll put it up here at the top, take LastName, put it at the right, and I'll drag Lifetime Sales and I'll put it to the right of that.

Now the labels that came with them, right now they're overlapping a little bit. I can have those repeating every time I have a new state, I will have the labels with the columns of data below. But unfortunately, you can't move these labels just directly across from the Detail section to the StateName Header. You try and drag them using the top-left anchor, it'll just hit that wall, and it won't move across. So what we can do is copy and paste them up in the State Header section. I'll select each of the labels; the FirstName label, and I'll Shift+Click on LastName Sales and this FirstName label here. I'll press Ctrl+X to cut.

I'll select my StateName Header section and press Ctrl+V to paste those in. Now, we'll just line them up. I'll move Lifetime Sales right about there, the LastName field, I'll move it right about there, and the FirstName one, I'm going to move that right about here. I'll move my StateName Header up and I'll align these underneath. Now, we can go through and fine-tune the alignment as we continue to further refine our report. Let's go ahead and just take a look at the functionality. But, before I do that, I'm going to take this Detail section, I'm going to make it really narrow, and we'll take a look at this in Print Preview Mode here.

Now, I can see I've got pretty respectable looking report already. I mean, the fields are in the right spot, essentially I can move them around and refine the layout. But, it's giving me the results that I was looking for. I've got the StateName here, Alabama, and I've got my customers sorted underneath. So I've got Rylee Whitfield, and the Lifetime Sales. It looks like I've got this reverse though from the way I really wanted it. I wanted the highest value at the top, and the lowest value at the bottom. So I can go and change that in the Grouping & Sorting options. Close Print Preview, I'll turn on in my Design Tab, Group & Sort, we'll take my Sort by Lifetime Sales, and I'll change it here.

Now, let's add a couple of other items to my report. We can add a title into the Header section here using a label. I'll click on Label, click there once to type in the title. Title for this is going to be Lifetime Sales by State. I'll press Enter. Now, I can format my title. We'll change the font to 22. I'll change the color from gray to this darker gray here to be 15% dark. I'll make this section a little bit bigger, and I'll make my textbox bigger. We can also add lines to help break up this space. I'll go to my Design Tab, and grab my line object, here.

I'll add a line to the StateName Header. So every time I have a new state, I'll have a line that's marking out that space. I'll click-and-drag out a line. We'll go ahead and rearrange these a little bit. I don't necessarily need a label for the StateName, it will be obvious that, that's the StateName. So I'm going to delete the StateName label and I'll move the StateName field over. I'll go ahead, and format that, I will make it bold, and then I'll make it a little bit bigger here. I'll take these values here and I will line them up. I'll click on each of these LastName fields, all six at once.

I'll go to the Arrange Tab>Size/Space>To Widest, to make them all the same size, then I'll line them up all against their left edge. So I'll select these two, Align>Left, these two, Align>Left, and these two, Align>Left, and maybe I'll move the LastName over a little bit. Okay, let's take a look at our design. Go to the Design Tab, and Print Preview, and there's our design looking up. I am going to go ahead and get rid of those boxes.

We'll close the Print Preview. I'm going to press Ctrl+A to select everything. We'll go to Format>Shape Outline> Transparent to get rid of the boxes around everything. Let's take a look at the Final Layout here; Print Preview, and that's what our report is looking like. So with a very little effort, we've already got a pretty respectable looking report. By starting from scratch, we didn't have to tweak any of the pre-made pieces that the wizards tend to put-in, and we're able to make it to our specifications right from the start. In the next movie, we're going to continue building on this form by looking at the conditional formatting rules that we can establish.

There are currently no FAQs about Access 2010: Forms and Reports in Depth.

Share a link to this course
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.
Upgrade now


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.

Upgrade now

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 preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



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.

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