24. (Advanced Programming In Access 2013) Adding Charts & Graphs To Reports 🎓

hello everyone and welcome to programming in Access 2013 the Advanced Course my name is Steve Bishop and in today's video we're going to be talking about adding charts and graphs to your reports now adding charts and graphs to your reports can help give a visual representation of the data to your users this is especially helpful for people who are more of the decision-makers right within the company they tend to kind of get that glossed over I'd look when they receive a report that's nothing but numbers and letters right it's just one big long report full of a bunch of information and what ends up happening of course is that they pull you into a meeting and say what does this report mean right because they don't quite grasp what all the numbers mean to them so a chart or a graph can really help give a visceral feel to the report kind of give you a good visual representation of what does that data really mean what are the strengths or the weaknesses within the company can be shown very well with a chart okay or with a graph so what we're going to do is I'm going to show you as an example today we're going to do a pie chart for each customer on a report and inside of that pie chart is going to be the quantity of each product sold to that customer and so what we're going to do is first we need to collect that data right we need to go out and get and collect what is the information that we need to put into that pie chart so we're going to start off with a query we're going to go to create tab query design and in order to get those three different key data points again we're getting the customers we're getting products and we're getting the quantity of each product right because that's what the criteria was for this report in order to get those three data points we need to get the customers table we need to bring in the order details table the orders table and the products table and I'll show you why we need all four of these here in just a second so I'm just go ahead and click Add just drop this down here and a little bit of arranging here so you can see now the orders table in the Northwind database is a what we often refer to as the order header okay is it a header table and then the details of the order details is often what we call a details table and what that basically means is the orders table here just contains the overall information related to that order so you've got the employee that's putting the order together you've got the customer that the order is for the order date the ship date where is it being shipped to etc etc so it's an overall information about the order but the order details table is where you actually have each individual line item of the product purchased and how much so you've got the product you got the quantity how much did each each quantity cost any discounts the when was it allocated etc etc now you'll notice though that on the orders table here we don't have the customer name instead we have a foreign key value that points back to the customers table so let's go ahead and inner join the customer ID field to the ID field of our customers table and then additionally we have the same situation over here on the order details table we only have a product ID we don't have the name of the product so we need to go ahead and do an inner join of the product ID to the ID field on our products table and now we have appropriate joining of all of the different tables together inside of our query so let's go ahead and now add our three different data points we wanted the company name we wanted the product name and we wanted the quantity of that was sold right now there's a bit of a problem here the order details table has a different row for each time that there was an order so we're not really getting a good sum of the total number of products sold to that particular company right it's not an aggregate in other words so in order to make this query and aggregate so we can get a sum a summation of all of the quantity of that product that was sold to the company we need to go ahead and go up to this totals button here and click on it so that we can change this to an aggregate query now we do want to go ahead and group our aggregate query according to the company and by product name but we want to change this quantity to a summation so we're going to change that to sum so we're getting a sum total quantity of each product sold to each company and that's going to give us our three data points in the way that we want it there are a couple of things about this query that I do want to fix up though because if you look here we've got this Northwind traders thing in here in front of our as part of our product name for each one of our products and that's kind of annoying okay we don't really need to know this Northwind traders we just need to know almonds beer rose and various spread boysenberry spread etc that's all that we really need for our product information and then additionally if you look at the quantities of each one sold you'll see that there's a couple of entries in here where there's zero sold so we don't really need company C's entry of Ghannouchi where there's zero quantity so we need to exclude that out in our query so I'm going to go back into the design view let's change these two things up first for the product name I'm going to change this a column to just product and I'm going to do a replace function on the product name field and we're going to replace anywhere where you see Northwind traders with an extra space at the end there we're going to replace that with a empty string and that's going to be our replace function and for our quantity I'm going to say the quantity has to be greater than zero and let's go ahead and view the data now and sure enough there we go so there is each product sold to each company and the amount sold okay and that looks pretty good I don't see any zeros for the quantity our product a product maybe I need to fix that huh that some of you guys watching already caught that said steve gosh you got to fix that you can't spell product there we go okay so I think this query looks pretty good let's go ahead and save the query or call it Q R Y for query customer products quantity okay let's go ahead and save that and close that so we've got our query now that has the data that we need for our pie chart now we need to go ahead and create our report that the pie charts are going to go in so let's do report design and let's just kind of adjust the real estate here a little bit I'm going to go ahead and add a title to our report let's do products products sold per customer and I'm just going to kind of change a few things here let's go to woops format tab there we go you just Berlin sans make it black and bold and change it to size 20 and resize that so it looks right okay and let's change the page header color you don't have to do this I'm just doing it just to make it look better I think let's change that to green and okay so a few things that we need to think about here when we're designing our report the details section we want a new detail section for each customer because if you recall the way that we talked about how we want this report to display is we want each but we want a new pie chart for each customer right if we want a pie chart that displays the quantity of each product sold to those customers so we need to separate out this report to show each individual customer that our own pie chart the way you do that is you need to go ahead and set some sort of record source here on your report that's going to give us a new detail section for each customer and the way you do that is you're going to go to the record source section and I'm just going to go and click on the ellipsis here and I'm going to go ahead and grab that query that we just built because it's going to show me only the company names that have some sort of product solds have some sort of quantity remember we had where the quantity had to be greater than zero so these are the only companies that I'm really interested in for this report but there is also an additional problem if you think about this the query customer products quantity query that we built has a new record for each product right so there is a record for each product so you may have the company listed multiple times so what we need to do is we need to additionally make this an aggregate so I'm just going to click on the totals here and we're going to group by company if I took this off just to show you if I just ran this you'll see company a shows up multiple times that's because we have different products for company a that have been sold so I'm doing a group by on that just so that we get each individual company name you could do a select distinct if you wanted to first those of you guys who are sequel query savvy know what distinct means but I doing a group by is just as good so let's go ahead and save this and close it and now I'm going to go ahead and add a text box with the company name in it to our details section so that each time that this details section comes up we can see what the company name is so company name is the label and I'm going to add and I make this nice and wide here and we need to bind this the control source for our text box to company and we also need to change this to txt company because we need to change this this field here this text box just makes things a little bit easier okay so we've got our company name in there good and bring this up a little bit so I just want to show you if we do a print preview here you could see company a company a a Company B BC D D D etc etc so the each have their own detail section that comes up and we're going to put a pie chart underneath each one of those things one other thing I just like to do with my reports just because sometimes they look ugly I'm going to change this this company text box I'm going to change the format to get rid of the background make it transparent same with the border so now it looks a little bit better right so now we don't have that big old square around the company name and then one other thing I'm going to change is I'm going to get rid of the alternate background color so that it's all one solid color here okay so that looks pretty good there so we've done all of this setup work just to add this chart and now what is it ten minutes into here we're going to actually do the adding of the chart so go ahead and go to the design tab here and if you go down one you're going to see this three bar chart right here go ahead and click on that and I'm not going to drag and drop or do any sort of drawing of the size that I want this I'm just going to click once inside the detail section to drop in that object now we're going to go ahead and get this chart wizard and typically I avoid wizards of all sorts but really when it comes to putting charts and graphs in here this is really the best way to do it so I'm going to go ahead and select it's asking me what I want to use for the data do I want a table or a query where I could even use both at least to fill in this particular section here I'm going to go ahead and select the queries and go look for that query that we just built there it is right there and click on next now this can seem a little bit tricky but we need all three of these fields so I'm going to go ahead and just click on this double arrow that's going to add all of the fields that are in this available fields over here to the fields for the chart now the reason I say this can seem a little tricky is some of you may be thinking well my pie chart only needs to have the product and the quantity of each product in it because really a pie chart only shows two things right it can only have two points of data on it it can only have you know what is each section of the pie going to be split up by which would be it's going to be split up by the product and the amount that's going to be shown for each section of pie right so that's going to be the quantity so why do we need the company filled right because our pie charts only going to show these two things product and quantity why do we need the company filled additionally as part of our pie chart well the reason is is because in order to have the the report filter the pie chart by each company so that each new detail section that shows each individual company you have to have some sort of data point within the pie chart object that you can associate back to the report and we're since we're filtering we've got this parent-child relationship between the company name appear in the report we also need to have a child field of company in the pie chart in the data for that fills in the pie chart so that we can say hey when you're filling in the details section of the report according to this company additionally filter out the pie chart data by company so we need to add this company field as a field on our pie chart even though it's not actually going to be displayed on the pie chart okay so click on the next button here and now I have the option to select any number of these different types of charts or graphs so you've got you know you've got your 3d columns you've got your regular columns you've got cylinder columns you've got 3d cylinder got call cone columns there's a lot of different options here I think there's 20 here I'm going to go ahead and select the pie chart just a two-dimensional pie chart here but you could select any number of one of these here but it really does take some time to figure out what's the best chart for the report you're running and really it's a good idea to kind of run through at different tests in order to find out which chart really fits your data the best so I've already done that and that's why I've gone with pie here is a two-dimensional object I'm going to go and click on next here and you'll see that now the the chart wizard is going to ask me what are my two different data points to fill in this two-dimensional pie chart and it automatically associated company with the quantity right so we've got the company and quantity on here but this isn't really what we wanted remember we want the pie chart to show each individual section is divided by the product along with the quanta amount filled for each one of these is going to be the quantity so the way you can change this is I'm just going to go ahead and click and drag that company section there back to where we have our different data points over here on the right you can see that changes this to say series I'm going to do the same thing with the sum of sum of quantity and drop that back in over here now you can see we have series and we have data well the series is going to is where you're actually stating what the different sections of your pie is going to be and we're separating our pie according to the product and then the amount that each section is going to be it's going to be filled in by the quantity so I'm going to drop that in here you can see access just goes ahead and does a summation of whatever the value is that I drop in here but it's really just doing a sum of a sum so it doesn't really make much of a difference but that's okay so we've got our two different data points entered in here for our pie chart we can click on this preview chart button here and you have to understand this is not yet filtering out according to any particular company right it's just doing all of the products and the quantities you can see there's a lot of beer that's sold here and I don't know what this blue thing is but there's a lot of beer that's being sold by Northwind traders okay so that's just kind of a preview of what the turret looks like but again it's not being filtered yet by the company name it's going to close that click on next and now here is where you set up your parent-child relationship between the pie chart that goes here and the company name or the details section of your report and access already kind of figured out hey you've got a report field here called company we also noticed on your chart field you've got a company field on your chart field you've got a company field so are these two things associated and that's correct right that's that's obviously what we wanted so here it's already setting up our parent-child relationship now let me just tell you this particular window that you see here where we're setting up the parent-child relationship this would not appear if I did not already have some bound fields on my report if I did not already have some sort of data here on my report and some bound fields so it's important that you set that up first on your report before you go through this wizard so that you get this parent-child relationship that shows up here while you're going through the wizard okay so our parent-child relationship looks correct I'm going to go ahead and click on next and now it's saying what title would you like for your chart so this title is actually going to show up here at the top of the of the chart and it's going to be centered so Center top and I do want to go ahead and have a title here so let's go ahead and do a products sold and I also do want to display a legend which is for those of you don't know what a legend is it's that little box or the little thing that tells you what the different colors mean so when we pulled up the example it had the different colors of the pie and then off to the right it had some sort of indication of what each different color meant that's what the legend is so I'm going to go ahead and say yes I do want to display a legend with my chart go ahead and click on finish' and there we have kind of a not a very it's not a very accurate obviously we don't have east west and north that's not what our chart actually shows but it does have the right title okay so that's correct I'm gonna make this a little bit bigger here and okay so I think that looks pretty good as far as size wise for our report let's go ahead and take a quick peek at it in the print preview section okay so company a has products sold it looks like chai and coffee crabmeat looks like they sold mostly crab meat so that looks good and if you scroll down here so company a looks like fit but mostly beer the majority of products that Company B B's bought was coffee and that's the end of page one so then we have page two we have page 3 page 4 etc so you can see this is a very good report this really tells you according to each company what is the majority of the products sold so there you go if you guys have any questions about how to do this if you have any you know any questions about this video in particular or if you're working on your own thing and you just kind of want a quick little piece of advice feel free to drop me a comment in the comment section of this video and I'll be happy to do whatever I can to answer any questions and again if you guys have any knowledge about these things please feel free to share it with the rest of the community it can be very helpful to have other people chime in and answer people's questions so that I'm not the only one trying to help out okay well if you have it like I said if you have any questions please feel free to drop in something in the comment section and as always don't forget to Like favorite and subscribe just to let you guys know the next video we'll be doing charts we're going to do another pie chart but we're going to do it on a form

Channel: Programming Made EZ

Views: 81,432

Rating: 4.9191918 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, microsoft SQL server, SQL server, programming language, report, chart, charts, graph, graphs, Data

Id: 2WKn_4LZh4g

Length: 21min 39sec (1299 seconds)

Published: Sat Aug 22 2015