|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Percent of Count By Richard Rost 
5 years ago
Calculate Percentage of Total Count of Records In today's video, I'm going to teach you how to calculate the percent of total count that each item makes up in any particular table. For example, I've got a table of projects. Each project has a status (planning, design, construction, quality control, and completed.) I want to know the percentage of projects in each status type. Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, aggregate query, dcount, outer join, nz function, Query to get percentage of records in table, calculating a percent of total in a query, Adding a percent column to MS Access Query
IntroIn this video, I will show you how to calculate the percent that each item in a Microsoft Access table makes up of the total count, using a real-world example with project statuses. We will build aggregate queries, use the DCount and NZ functions, work with relationships between tables, and set up outer joins to make sure all status categories are included in the results, even those with zero counts. You'll also learn how to format the output as percentages for easy analysis.TranscriptWelcome to another FAST-TIT video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to calculate the percent of total count that an item makes up in any particular table.For example, here we have a list of projects and what the project status is. The status is either planning, design, construction, quality control, or completed. I want to know what percentage of my projects are in each phase. As you can see here, planning makes up 3 of them. I got 1, 2, 3 out of 8. So in this video, I'll show you how to calculate that. Now before you get started with this video, you should know all of these topics: aggregate queries, the DCount function, relationships between tables, outer joins, and the NZ function. If not, I've got free videos on my website, on my YouTube channel. Go watch these first. I'll put links down below in the description below the video so you can just click on them. Let's say I've got two tables. One is my project table that has the project ID. That's my auto number, the project name, and the project status ID. Status ID is a number, a foreign key that relates to the project status table. This shows what the status of that project is: planning, design, construction, quality control, or completed. One through five. They're all right there. Now, the goal is to figure out how many projects I have at each phase and what percentage of the total they represent. I can see here, for example, I got 1, 2, 3 that are in the planning phase. That's planning. Three out of the eight that I have are in the planning phase. I can figure out what the percentage is. Let's start by building an aggregate query to figure out the count for each project status. So let's go to Create Query Design. Let's bring in the project table, close that. Then we're going to bring in project status ID twice. Turn on Totals to make it an aggregate query, and now set the second column to count. When I run this now, I'll get each project status ID and the count of how many there are for that project status ID. There are three projects in status one. There are two projects with status three. I'm going to save this as Project Completion Q. And now we'll close this because we have to do a calculation, but we can't put the calculation here in the aggregate query. We need another query because this value hasn't been calculated yet. So this query has to finish, then we can take these results and feed them into another query. So close that. Let's go to Create Query Design. Let's bring in the Project Completion Query that we just built. Close Add Tables. Bring in the project status ID and the count of project status ID. So if I run this now, I'm getting the same values as the other query produced, basically. But now I can do some math. So I'm going to come over here and I'm going to say percent total is going to be the count of project status ID divided by, now I need the total count of all of the records in this table. So I'm going to use the DCount function, which is going to go out to the table and count up the records in that table. Let me zoom in so you can see better, Shift F2. It's going to be DCount star inside quotes from the project table. This will count up all of the records in the project table. That should return an eight. Hit OK and now run it. Look at that. Now we just have to format that as a percentage. So let's come back in here, right-click Properties. We're going to go to Format, Percent, and then maybe one decimal place. And now I'll run it and there we go. There are our percentages for each project status ID. Let's save this as Project Completion 2 View. Now you might notice you're missing one status ID there, status 2, because we don't have any projects in that phase. But I still want to see a zero there, don't I? How do we do that? Well, let's go back to Design View. I'm going to close this property sheet. I'm going to bring in the project status table. So go to Add Tables, ProjectStatusT. Right there it is. I want to see all of these guys. So I'm going to bring in the project status ID from this table. And I'm going to change the join type because right now if I run this, I'm still missing values. So I'm going to change this join type, double-click. We're going to make this an outer join. I want to see all of the records from the ProjectStatusT and the matching records from the query. So hit number three there. This is an outer join to show me all of these and the related ones over here. And if I run this, there you go. I've got one, two, three, four, five. And notice we're missing a record over here. Now we can't do math on a null value. So I'm going to convert this null over to a zero using the NZ (null zero) function. So come back out here and let's take a peek at this guy. I'm going to start by renaming it. Let's call this total count. It's going to be colon. Let me zoom in so you can see it better. Total count is going to be NZ of this guy, and if that is null, make it a zero. That's what NZ does. If this guy's null, make it a zero. That can be what total count is now. So I'm going to replace total count in my function over here. OK. You're going to be total count divided by the DCount of that. And of course, you can't divide by zero. So this query assumes that you have at least one record in the table. Otherwise, you will get an error. You could throw an NZ around this if you want to, NZ around this guy. Here, I'll zoom in, just in case you have no records, but that's not really necessary. And now when I run it, I get an actual percentage there for my missing record. And now if you want to, since I've got the ProjectStatusT in here, I can bring in the name, and I can see the project name there too. So now in whatever form or report you want to base on this, you could bring in the project status name. I bring in the ID too, so you can sort them like that. And then you can show the percent total right next to it. And there you go. And that should add up to 100. If you enjoyed this video, please be sure to give me a like and share and post any comments that you might have below. Make sure you subscribe to my channel and click on the bell icon to receive notifications whenever new videos are posted. Be sure to watch my free Access Beginner Level 1 course. It's over four hours long and you can find it on my website or on my YouTube channel. I'll include a link below you can click on. Thanks for watching this FAST-TIT video brought to you by AccessLearningZone.com. I hope you learned something today and we'll see you real soon. QuizQ1. What is the main goal demonstrated in this video?A. To count total records in a table B. To calculate the percent of total count for each status in a table C. To create a relationship between tables D. To import data from another database Q2. Which Access query type is used first to count how many items exist in each project status? A. Action query B. Aggregate query C. Crosstab query D. Append query Q3. In the example, which table lists the possible statuses like planning, design, or completed? A. ProjectT B. ProjectCompletionQ C. ProjectStatusT D. StatusCountT Q4. What function is used to get the total count of records in the project table? A. Count() B. DSum() C. DCount() D. DSCount() Q5. Why do we create a second query after building the initial aggregate query? A. To import more tables B. Because calculations on aggregate values must be performed in a separate query C. To connect to an external database D. To export data to Excel Q6. If a project status has no associated projects, which value shows up in the aggregate query? A. Zero B. One C. Null D. Minus one Q7. What function is used to convert a null value to zero when missing data is present? A. Zero() B. IIf() C. DNull() D. NZ() Q8. Why is an outer join used between ProjectStatusT and the query results? A. To show only records with matching project statuses B. To display all possible statuses, even those without projects C. To delete unmatched records D. To restrict the results to completed projects Q9. What formatting is applied to the percent total field in the query? A. Currency B. Short Date C. Percent with one decimal place D. General Number Q10. What assumption is made regarding the presence of records in the table when dividing by the total count? A. At least one record must be present B. The query will automatically handle empty tables C. Division by zero is always handled by default D. Access ignores empty tables Q11. Once the final query is complete, what should the sum of all project status percentage values equal? A. 50 B. 75 C. 100 D. 0 Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-D; 8-B; 9-C; 10-A; 11-C 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 calculate the percent of total count that each item represents in a table using Microsoft Access.Let me give you an example. Suppose you have a list of projects, and each project has a status such as planning, design, construction, quality control, or completed. The goal is to determine what percentage of your projects falls into each of these phases. For instance, you might have three projects that are currently in the planning phase out of a total of eight, and you want to calculate what percentage that is. Before proceeding, make sure you're comfortable with aggregate queries, how to use the DCount function, understand relationships between tables, know how to work with outer joins, and are familiar with the NZ function. If you need more background on any of these topics, I have free resources for you on my website and YouTube channel. Let's break down the setup. Imagine two tables. The first is your project table, which contains fields for project ID (set up as an AutoNumber), project name, and project status ID. This status ID is a number that serves as a foreign key linking to a project status table. The project status table then lists the different statuses by ID, such as one for planning, two for design, and so forth. The task is to count up how many projects are in each phase and then determine what percentage each group represents out of the overall project total. Start by creating an aggregate query to count the projects for each status. In Query Design, add the project table, and bring the project status ID into the design grid twice. After enabling Totals to turn your query into an aggregate query, set the second column to count. When you run the query, Access will show you each status ID alongside the number of projects at that status. Save this query under a suitable name, such as Project Completion Q, and close it. Since you cannot include your percentage calculation in this aggregate query, you need to construct a new query that uses its results. So, create a new query and add the Project Completion Q you just made. Add the project status ID and the count of project status ID to the grid. Now you can include a calculated field for percent total. Here, you divide the count for each status by the total number of records in the main project table. Use the DCount function to get the full count of records in that table. This value acts as your denominator. Format the calculated field to display as a percentage, with one decimal if you like. At this stage, you will see the percentages for each project status ID. Save your new query with a name like Project Completion 2 View. You may notice that certain status IDs are missing from the results, for example, if there are no projects currently in a particular phase. The result skips those rows, but you might want to display them with a zero instead. To accomplish this, return to Design View and add the project status table to your query. Next, adjust the join type between the project status table and your aggregate query so that you see all status IDs, even those with no matching projects. Specifically, use an outer join to include all rows from the project status table and matching rows from your aggregate query. Now, when you run the query, statuses with no projects show up, but their count field is null. You cannot perform math using a null value, so apply the NZ function to convert these null values to zeros. Assign this result to a field called total count. Wherever you previously used that field in calculations, update your formula to use this new total count. Remember, the query expects to find at least one project in the table. If the table is empty and you attempt to divide by zero, there will be an error. You could wrap your calculation in another NZ just in case, although it is not usually necessary. After making these adjustments, the output now displays a percentage value for every status ID, including those with zero projects. If you wish, you can also display the status name along with the status ID for easier identification and sorting. This makes the results more readable, and ensures that the percent total for all statuses adds up to 100. For more detailed instruction and step-by-step guidance on how to handle these calculations and queries in Access, you can find a full video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListExplaining the goal: calculating percent of total for status phasesBuilding an aggregate query to count items per status Saving and using the aggregate query for calculations Creating a follow-up query to perform percent calculations Using the DCount function to get the total number of records Formatting the calculated value as a percentage in the query Handling missing status values using an outer join Adding the status lookup table to display all possible statuses Using the NZ function to handle null values in counts Updating calculations to avoid math errors from nulls Displaying the project status name alongside percentage results |
||||
|
| |||
| Keywords: FastTips Access percent total count, aggregate query, dcount, outer join, nz function, Query to get percentage of records in table, calculating a percent of total in a query, Adding a percent column to MS Access Query PermaLink Percent of Count in Microsoft Access |
