|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Percent of Total in Charts By Richard Rost 
6 years ago
Display Percentages in Queries, Modern Pie Charts Learn how to display percentages in your queries, and in the new Modern Charts in Microsoft Access. Brent from Santa Ana, CA (a Gold Member) asks: I'm trying to create a pie chart in Microsoft Access using these new Modern Charts. I cannot figure out for the life of me how to see a percentage value for the data label. I can do it in the old Access charts, and I can do it in Excel, but I can't figure it out in these new Modern Charts. Help! MembersI'll show you how to automate the chart type selection so your users can pick from a list to change the chart to pie, line, column, or bar.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksAggregate Queries: https://599cd.com/aggregate
IntroIn this video, I will show you how to display percentages in pie charts using the new modern charts in Microsoft Access 2019 and Office 365. We will talk about the differences between the classic and modern chart controls, look at how percentages can easily be shown in Microsoft Excel, and learn how to create a query in Access to calculate percent of total values so you can use them as data labels in your modern charts. If you want to see percent of total by category in your Access charts, this video will walk you through the process.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to display percentages in charts.In the modern charts that Microsoft just introduced in Access 2019 and Office 365, you can create some pretty cool-looking pie charts, but there is no option for showing percentages. So, in this video, I will show you how to do just that. Today's question comes from Brent from Santa Ana, California, a Gold Member. Brent says, "I'm trying to create a pie chart in Microsoft Access using these new modern charts. I cannot figure out for the life of me how to see a percentage value for the data label. I can do it in the older Access charts, and I can do it in Excel, but I can't figure out how to do it in these new modern charts. Help!" You are right, Brent. The new modern charts that were introduced in Access, I believe 2016, are pretty cool-looking, but they are still in their infancy. They still have a lot of work to do on them. Excel definitely has a lot better charting options available. So if it is something that you can copy over to Excel, you will get a lot better charts out of Excel. But let me show you how to do it in Microsoft Access in case you want to throw together a quick chart. I like to put my charts on my menu for a dashboard so I can see certain things like sales, and Access charts are just fine for simple little charts like that. But you are right, there is no percentage option in the pie chart, so we have to use a little trick in a query to get the percentages and use that as a data point. Here I am in Microsoft Excel, and I have my sales for the last 12 months broken down by category. I want to put this in a pie chart and see what percentage of my sales came from each category. In Excel, it is super simple. Just select all the data for the chart. Go up to Insert. Under Charts, find the chart you want. Here are pie charts. I will do a simple 2D pie chart. Click on it. There is your chart. Now, to turn your data labels on, just right-click and go to Add Data Labels. That will put them on there in dollar amounts. If you want to see percentages, right-click, go to Format Data Labels, and right here you will see Value and Percentage. Just turn Percentage on if you want both of them, and you can turn Value off if you do not care to see the dollar amount. And there you go. It is that easy to see percentages in your charts in Excel. That was really pretty easy. Now, let us move over to Access. Let us say you have all your information, the same data, in a table in Access. Usually in Access you will have your Orders table. It has all the individual orders and the order amounts, and you can use an aggregate query to join all that information together by category and sum up your amounts. I have other videos to explain how to do that. What you want is an aggregate query, also known as a totals query. I will include links down below in the description for other videos that cover how to get this summary information. But assuming you have this, how do we put this into a chart in Microsoft Access? Let us go over to our Main Menu form. Now, this is my simple blank customer database. You can download a copy of this template from my website. Again, you will find a link down below. I am just going to use this Main Menu to throw a chart on it. Let me first show you the old style charts. Let me go over to this label here too. Actually, we do not need these buttons either. Goodbye. If you go to the toolbox right here, there is a chart control right there, which is different from the modern chart option, which is out here and was recently added, I believe in Access 2016. Pick the old chart first. Let me show you how the old chart works. The Chart Wizard appears. Where do you want to get your data from? OrderT. Next, what do you want in the chart? The category and the amount. Do not really need that ID in the chart. What do you want? Let us do a pie chart. OK, category, sum of amount. It guessed those correctly. That is fine. Hit Next. What title would you like for the chart? Orders by Category. And do you want a legend? Sure, why not? And then Finish. There it is. It is not very pretty. That is one of the reasons why Microsoft is redoing them with the modern charts, but it is functional. You do not really see a summary here; it does not give you a preview. Let us save this, close the Main Menu form, and then reopen it again. There you go. Again, it is not very pretty, but it works. These actually look better if you make them bigger. These are designed mostly to be printed out. Let me close this menu here. If you make this guy big, it looks a little better. These are usually designed to stick on your reports or make full screen charts. There. It looks a little better. Now you want data labels on it. Right-click, Design View. Right-click on this guy. Go to Chart Object Edit. That puts you into chart editing mode. Again, real old school here. This is like Excel 2000-ish. In here, just right-click, Format Data Series. This menu comes up. You can change the colors and all that stuff, but under Data Labels, you could put the value on or you could put the percentage on. Hit OK. Now you can see them in the preview. Save it. Close it. Open it back up again. There you go. That is that easy. Even the old school, old access charts give you percentages. Now let's take a look at the modern charts. I am going to close this one. I am going to copy this guy. Just copy and paste. We will call this Main Menu. Actually, we will call this ChartF. ChartF. I like to save these so the Gold Members can download the database. All right, ChartF Design View. Let us delete this guy, and let us recreate the same thing using a modern chart. I am going to shrink this back up just a little bit. It is easier to work with, and I can put the menus open. Insert modern chart. Pie. Drag it out where you want it. This thing appears. We are going to pick from a table. We are going to pick from OrderT. There it is. Looks good so far. Set our value. Our Y-axis equals Amount. Now we can turn data labels on. Go up to Format and then pick Display Data Label. There is the data label, but it is dollars. It is not percentages. There is nowhere in here to change that option to percentages. That is the chart settings. You can also open the properties for this object. Right-click Properties. There it is. We can close the chart settings. There are a ton of properties in here. None of them will set percentages. One of them, if you scroll down, there is a primary values axis format right there. You can set this to percent, but that just multiplies up by 100. That does not do anything. Get rid of that. I have scoured these settings. I looked in here. I googled it for a while. I cannot find any way to turn on percentages. So if you have something, by all means, post a comment. Let me know. What we have to do in that case is we have to make our own percentages in the query, because I cannot figure out any way to do it. If you have something, then by all means, let me know. Delete this guy. We do not want that. I am going to save this chart form and close it. Let us make a query. Create Query Design, bring in your OrderT or whatever data source you are using. We need Category and we need Amount. If I run the query, I get this. What I want is this as a percentage of all of these values. I am going to need to know how to sum all of these up. I can use DSum for that. I am going to call this PerC, meaning percentages. This will be the Amount field divided by DSum. DSum, what field? Amount. From what table? OrderT, or a query if you are using a query. We do not need any criteria because we are just pulling them all in. Take the amount right here and divide it by the total of all of the amounts. Save this as SalesPercentByCategoryQ. When I run this, that is what you get. You can see this guy here is 52 percent. If that is too big for you, let us break it down. Let us round this number off. Round that to maybe three decimal places. There we go. There are our percentages. That looks like about 52 percent of the whole. 14 percent, 13 percent. Leave it like this because we will use that percent format over in the chart. You could multiply this by 100 if you want to see that as 19.1, for example. That is how you calculate your percentages and put them in a query. You might want to use this for other reports or whatever. That is a good step right there. Now we can use this value in the chart. Let us go back to our chart form. Design View. Modern Chart. Pie. Drag it right there. Queries this time. SalesPercentByCategoryQ, we are using the percentage as our data label. As our Y-axis, go to Format, turn on the data labels. They show up like that now. Go to the property sheet. You can shut down the chart settings now. Find that thing I showed you earlier, that format right there. You can change that to percent, and there you go. If you do not want fractions on there, you can just do something like this. You can go 0 like that and you will get it like that. Or if you want one decimal, you can go 0.0. See how that works? These look a lot better if they are bigger, like I said, even the modern charts do, so make that as big as you can. The Access team needs to add that as a simple, easy feature. I could not find a way to do it without doing it this way, but now we have a solution. There is still no easy way in modern charts to come in here and change the individual pie slice colors or things like that, so these are looking really cool. They are coming along, but they still have a lot of work to do on them. In fact, I have touched on these really briefly in my full classes, but I really have not done a lesson on the modern charts yet because I am waiting for them to finish them. There is a lot they still have to do to finish these guys. When they are done finishing them, then I will do a full lesson together on them. Oh, and by the way, I just Googled it, and the modern charts were added in 2018. They are in either Office 365, if you have a subscription, or they are in Access 2019. Want to learn more about these charts? In the Extended Cut for members, I am going to show you how to make the chart changeable by the user. We will put a little list box there with four different types of charts: Pie, Bar, Column, and Line. They can click on that little list box and change the type of chart it is. Here it is: Pie, Bar, Column, Line. There are lots of other options you can automate. I am just showing you one of them in the Extended Cut. That is the Extended Cut for members only. Silver members and up get access to all of my Extended Cut videos. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my Extended Cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free. QuizQ1. What is the main limitation of modern pie charts in Microsoft Access compared to Excel?A. They cannot display dollar values in data labels B. There is no built-in option to show percentages in data labels C. They cannot create pie charts at all D. They can only use one color per chart Q2. What is the suggested workaround for displaying percentages in a modern Access chart? A. Change the chart type to a bar chart B. Manually edit the chart design in Excel and re-import it C. Calculate percentages in a query and use them as the data source for the chart D. Add a text box next to the chart with the percent values Q3. What function is used to calculate the total sum of amounts across all categories in Access for percentage calculation? A. DLookup B. DSum C. SumIf D. DCount Q4. Where can you change the format of data labels to percentage in an Access modern chart? A. Chart Wizard settings B. Form Property Sheet by setting the Primary Values Axis Format property C. Right-click the chart area and select Format Chart Area D. Through the Record Source property of the form Q5. In the older Access chart control, where is the option to show percentages in a pie chart found? A. In the Chart Wizard data selection step B. In the Format Data Series menu under Data Labels C. In the form's main property sheet D. It is not possible in the older chart control Q6. What type of query should you use to summarize sales by category before calculating percentages? A. Cross-tab query B. Totals (Aggregate) query C. Parameter query D. Update query Q7. Why might you still choose to use Access charts on a dashboard, according to the video? A. Access charts can be easily exported to PowerPoint B. They are better looking than Excel charts C. They are functional for quick, simple visualizations on dashboards even if not as feature-rich as Excel D. Access charts support 3D animation Q8. What does multiplying the percentage value by 100 in the query do? A. Converts it to currency B. Displays the value as a whole number percent (e.g., 19 rather than 0.19) C. Divides the value by the sum of all categories D. Adds up all category values Q9. What is a limitation of modern charts in Access mentioned in the video, besides percentages? A. You cannot make the chart bigger B. You cannot change individual pie slice colors easily C. Modern charts do not support pie types D. There is no way to use queries as data sources Q10. In the Extended Cut for members, what feature did the instructor demonstrate? A. Importing charts from Excel B. Allowing users to switch chart types via a list box C. Coloring data labels based on values D. Sending charts in an email automatically Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to display percentages in charts using Microsoft Access. Specifically, I will walk you through using the new modern charts introduced in Access 2019 and Office 365. These newer charts look much better than the old style, but unfortunately, they do not have a built-in option for showing percentage values directly on pie chart data labels. If you have used the classic Access charts or Excel, you probably know how simple it is to show percentages, but the feature has not yet been included in these modern versions.One of our students asked how to get percentage values onto the data labels for modern pie charts in Access. He is able to do this easily in classic Access charts and Excel, but the option is missing from the new chart tools. He wanted to see if there was a solution for this limitation. While it is true that Excel's charting options are more advanced and flexible, sometimes you want to create a quick dashboard directly inside Access. For these cases, the Access charts are perfectly adequate, especially for quick visuals on forms like a sales dashboard. The challenge with the new charts is that although you can turn data labels on, you are limited to displaying simple values like sales amounts. There is no built-in way to show percentages alongside the data. To illustrate, I first show how easy it is in Excel. You simply select your data, insert a pie chart, add data labels, and choose to display the percentage. With just a few clicks, you can switch between values or percentages. This is not the case in Access, unfortunately. Moving to Access, the first thing you need is a summary of your data. Typically, you might have an Orders table with categories and amounts. You need an aggregate query to sum the data by category. This kind of totals query takes all your orders and summarizes them by whatever field you choose, such as product category. If you use the old Access chart object, you go through the Chart Wizard to pick your table, your categories, and your values. When you finish, you get a basic pie chart. It is not very attractive, but it does function. Even with these old charts, you have more options for customizing how your data appears, including the ability to show percentages as data labels. With the modern chart control, things have changed. First, you insert the modern chart, select your data source, set up your Y-axis values, and turn on your data labels. However, data labels are restricted to showing raw values (like sales amounts), not computed percentages. There is a property labeled "format" where you might expect to enable percentage formatting, but it only changes how the number is displayed, not what is actually shown. After checking every available setting and spending some time searching for a solution, it is clear that this feature is not available yet. To work around this, you need to calculate the percentages yourself in a query before feeding the results to the chart. You construct a query that sums the data by category, then divides each category's total by the grand total (using the DSum function, for example). This gives you a percent value for each category. You can round these as needed so they look clean in your chart. Save this query and use it as the source for your modern chart. Back in your form, insert a modern pie chart that uses your new query as its data source. Now, set the Y-axis to your percentage field, turn on data labels, and change the label format to percentage. You can adjust the number of decimals for better readability. With this approach, you have effectively created a pie chart in Access that displays the percent of each category making up the whole – just like in Excel. Even though the modern charts are designed to look better, they are still missing many features found in other programs. For example, changing individual slice colors and customizing the appearance is limited for now. I am waiting for Microsoft to finish building out these modern chart features before I create a comprehensive course on them, but this approach provides a functional workaround for the time being. As an additional topic, in today's Extended Cut for members, I will demonstrate how to let your users change the chart type dynamically. We will add a list box to the form that allows switching between pie, bar, column, and line charts. This lets your users pick the chart style that makes the most sense for the data they want to view. There are many other options you could automate, but I will focus on chart type selection here. If you want full step-by-step instructions, you can find the complete video tutorial covering all these steps on my website at the link below. Live long and prosper, my friends. Topic ListCreating pie charts in Excel with percentage labelsAdding and formatting data labels in Excel charts Inserting old-style charts in Microsoft Access Customizing data labels to show percentages in old Access charts Creating modern charts in Microsoft Access Using queries as data sources for Access charts Calculating percentages in an Access query with DSum Rounding percentage values in Access queries Formatting modern Access charts to display percentage labels Applying percent format to data labels in modern Access charts |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access percent, percentages, show percent, modern chart, pie chart, access charts, percent of total in query, charttype, dsum PermaLink Percent of Total in Charts in Microsoft Access |
