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

Adding charts

From: Access 2010: Forms and Reports in Depth

Video: Adding charts

The Chart control can give insight into your businesses metrics by presenting a dynamic graphical view of your data that changes as your database grows. Unlike exporting data to Excel and graphing there, creating your graphs directly within Access maintains the live and always updated link to your data tables and queries. Further, pairing a chart with a query gives you all the flexibility of adding query criteria to instantly refine the chart's dataset. In this movie we are going to create a chart object and in the next movie we are going to bring this chapter full circle and incorporate some other Form controls that will take user input to alter the chart's parameters.

Adding charts

The Chart control can give insight into your businesses metrics by presenting a dynamic graphical view of your data that changes as your database grows. Unlike exporting data to Excel and graphing there, creating your graphs directly within Access maintains the live and always updated link to your data tables and queries. Further, pairing a chart with a query gives you all the flexibility of adding query criteria to instantly refine the chart's dataset. In this movie we are going to create a chart object and in the next movie we are going to bring this chapter full circle and incorporate some other Form controls that will take user input to alter the chart's parameters.

First let's create a query that collects the data that we want to use in our chart. I will go to the Create tab and we will use the Query Design view. This will be a pretty simple query we are going to take a charge that takes a look at orders over time, we will use the Orders Table and say Add and then close. From the Orders Table I want information on the OrderID and OrderDate. Let's go head and save this query now, I'll close it and in the Save window and I say Yes. I am going to name this the qry_ OrderDates, I will press OK and it saved my query to my database. Now let's make a chart off of that data, I will go to the Create tab again and this time we will do a new form in Design view.

I am going to go ahead and close the Property Sheet and I am going to expand my Form area wider a little bit, to about the 10 inch mark and I am going to scroll down and make it a little shorter. Okay, so now the chart object is this control right here, it's shown with a three bars on it. I will click on it and then I will drag out a window where I want my chart, the chart Wizard starts up and it asks me which table or query do I want to use as the data source. I can choose from the tables that are my database, or I can switch my View to Queries here to select from the queries. There is the query we just built, so I will go ahead and say Next.

And then which fields do I want to add from the query into my chart. We will take both of them, so I will use the double arrow to move everything. Go ahead and say Next. This screen is asking us what type of chart we want to use, there are lots of different options here and a lot of these will work better for some data types than others. I am going to choose this basic line chart here. Go ahead and say Next and now we get a layout how our fields lay on the chart. Access tries to help us out a lot, but usually it gets this part wrong here, so I like to just take these fields and drag them off of the drop zones and just start with a fresh slate. So I'll drag both of these back off, that resets the chart area, and now I can set it up the way I wanted instead of the way Access thinks I want it.

I am going to take my OrderDates and put them on the x-axis on the bottom. I will just click OrderDate and drag it to down here and drop it down. When I do that Access automatically groups them by month if I wanted a different grouping level, I can double- click on it and choose it from here. So I could choose to Group by Year or Quarter or even Hour if I want. I am going to accept the default Month and say OK. The OrderID is going to go into the Data section here on the top left. When I drop it there, Access is going to aggregate based off of the account. Now if had numerical values, I could do sum or average as well. But since this isn't a numerical value, it's just going to count them up, and if I try and double-click on it to change it, it's going to tell me that.

Go ahead and say OK, let's go ahead and say Next. We can now give our chart a title, and I am going to name it Orders by Month, we could either choose to display a legend or not to display a legend, and I will just leave it on, and we will say Finish. That will put us back into our form, it's had the chart object inserted here, but this image that pops up actually has nothing to do with the data we just selected, it's just a placeholder image at this point, so don't get confused with these east and west and north. Let's go into Form view and take a look at the chart as it renders, we will switch our view to Form view. And occasionally in Access, the chart doesn't pop up, if it doesn't show up automatically you might have to press the Refresh button up here.

So there's a basic chart object and at this point it's pretty ugly, needs a lot of work in order to get it to work properly. If charting is something that is very important to your organization, then I would recommend looking into some of the third-party charting add-ins that are available for Access. For now though, let's see what some of the capabilities are with the built-in charts. So let's go ahead and edit this chart contents, I will double-click on it to open it up in the chart editing session. I am going to go ahead and close the datasheet, because I don't need to see what the data is. Now working with the charts is a little bit wonky, it's an older object within Access, and it's a feature that really hasn't been updated in several versions of the program, so it is going to be a little bit tricky to start getting these into the right size.

For instance, the fonts that I have here are really large, compared to the chart area up above, which makes the data illegible. But if I double-click on the text here, for instance, if I double-click on word September and go to the Font section, I will see that Access is trying to tell me that this is an 8 point font. The problem is it looks like its way too large to actually be in 8 point font. Let me go ahead and say OK. The issue with the chart here is that we are actually really zoomed into this chart, and in order to zoom out, we actually need to make the chart area bigger, so that Access will try and refit it back on to the screen. Let me show you what I mean, if I drag the width here, over here to the right, Access resizes the chart and it updates the size a little bit, let me drag it a little wider again, and it makes it smaller again.

Now this is going to take a little work and you might have to redo your charts a couple times until you get the hang of this, like I said, it's kind of a goofy user interface. Maybe I will make this a little wider now. And once I get the chart area looking a little bit like I wanted, for instance, now I could actually see the data, I could double click on the text again and now Access is telling me that the font is a lot bigger. At this point I can change it back to a smaller size, maybe 8 now, and say OK and it updates. Now that's looking a little too small, so I will change it straight 10. That's a better size, but now they are angled, double click on it again and this time we will go to Alignment and it's telling me that Access is trying to make it horizontal, let's force it to angled and then double click on it again, try horizontal again. There we go.

We finally got into a position that looks good, let's go ahead and double click here to change in size on the y-axis; I will go to the Font and change that to 10 as well, say OK. So I am starting to get a chart that looks a lot better, I am going to take this legend here, and I will click on it once, and I am going to drag it up, get it out of the way. And then if I click on the chart area anywhere between two bars in this white area, I will select the background of the chart, and I can make it bigger to fill up the space. Finally let me change the color here, these black bars in the background I think are really distracting from the actual data. I am going to double-click on the bar to change its color, here the color is set Automatic, and I am going to choose this light Gray-25%.

I will say OK and there it goes. It looks like it has lots more receded into the background and it really makes me focus on the data more. Now before we start editing this chart, now there is one more thing I want to add to this chart. If I go up to Chart menu I can choose that I want to add a Trendline here, when I click on that, the Add Trendline window opens and it asks me what type of line I would like to create. Some regression types or trendlines will fit some datasets better than others. For now I am just going to choose this linear regression type and say OK. And Access adds the Trendline into my chart and I can see that unfortunately we have got a downward trend in our orders.

Let's go ahead and click off of our chart anywhere in this white space out on the outside, and then I will return this back to our form. Let's go ahead and take a look at how this form is looking in Form View. I will change my view here to Form View and Access displays that chart, looks like we have a little bit of graphic issue here, we need to resize it a little bit. So, one more time, into Design View and I'll click on the chart area and I will make it a little bit bigger, that should fit better. Okay, go back to Form View and there is our chart. The chart object allows us to better visualize patterns that might be hidden in our data and adding in trend analysis and forecasting abilities can help you see a bigger picture of where you've been and where you're going.

Now in the next movie we are going to take this chart one step further. and incorporate some of the other controls to help us make this chart even more flexible.

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 · 14650 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 preferencesfrom 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.