Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91389 Viewers
80 Video lessons · 138265 Viewers
59 Video lessons · 57095 Viewers
52 Video lessons · 70746 Viewers
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.