Скачать или смотреть How to Add a Percentage Column in MS Access Queries

Learn how to effectively add a percentage column for each class based on stop codes in MS Access, even if you're a beginner.
---
This video is based on the question https://stackoverflow.com/q/72062834/ asked by the user 'pjpj' ( https://stackoverflow.com/u/18993008/ ) and on the answer https://stackoverflow.com/a/72064810/ provided by the user 'June7' ( https://stackoverflow.com/u/7607190/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: New column to calculate percentage

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Add a Percentage Column in MS Access Queries

If you are working with Microsoft Access and need to calculate percentages based on specific conditions in your data, you might find yourself needing to add a new column to your queries. Specifically, let’s say you have a dataset that includes a stop_code, with counts for different classifications like Early, Late, and On-Time. To better analyze this data, you may want to calculate the percentage representation of each classification based on the total counts for that stop_code. This post will guide you through the steps to achieve that.

The Problem

You have a dataset structured like this:

Stop_codeCountadh_Classi1001332Early10013101Late10013317On-TimeFrom this, you need to calculate the percentage of each classification against the total count for the stop_code 10013, which is 450. For instance:

Early: 7.11% (32/450)

Late: 22.44% (101/450)

If you are new to Access, this may seem daunting, but it's definitely manageable with the right approach. Let's delve into the solution.

The Solution

To add a new column with percentages, you can utilize aggregate functions in your queries. Here’s how you can do it step-by-step.

Using Aggregate Subqueries

One of the most direct methods to calculate percentages in Access is by using subqueries. Here’s an example SQL query that accomplishes this:

[[See Video to Reveal this Text or Code Snippet]]

Alternative Join Method

Alternatively, you can use an INNER JOIN to calculate the totals and percentages. Here's how that can look:

[[See Video to Reveal this Text or Code Snippet]]

Both options above will yield the percentage for each classification based on its count.

Using the DSum Function

Another effective method is to use the DSum() function, which can be simpler but may lead to slower performance on large datasets. Here’s how you can implement it:

[[See Video to Reveal this Text or Code Snippet]]

Creating a Report for Better Visualization

If you are creating a report, you might find it helpful to use the Grouping & Sorting design feature. You would calculate the sum in a footer textbox as follows:

[[See Video to Reveal this Text or Code Snippet]]

Then use this footer value in your expression in the detail section:

[[See Video to Reveal this Text or Code Snippet]]

This method may provide a clearer layout, especially for presentations or sharing insights with others.

Conclusion

Adding a percentage column in MS Access can greatly enhance your data analysis, allowing for easier decision-making based on the classifications. Whether you use subqueries, joins, or the DSum function, the right approach for you will depend on your specific dataset and performance considerations.

With these straightforward techniques, even beginners can handle advanced queries in Access. Happy querying!